SQL Query doesnt return right value - Xtreme Visual Basic Talk

03-02-2011, 08:16 PM

cuber_killua cuber_killua is offline

Contributor

Join Date: Sep 2004

Location: Philippines

Posts: 405

SQL Query doesnt return right value

hi guys!!! can you please help with this query, it seems that this query doesnt read the begindate and only displays record with enddate.

Code:

str = ""
str = str & "Select sum(quantity) as totalpurchase from purchaseorder where itemcode='" & itmcode & "' and"
str = str & " category='Consumables' and company='" & company & "' and (datereceive >= '" & datebegin & "' and datereceive <= '" & dateend & "')"
str = str & "  and (postatus='received' or postatus='oh-hand')"

i am using vb2010 and mysql community server.

__________________
Trion Towers and Woodsville Residences

The Radiance

03-02-2011, 11:09 PM

Qua

Impetuous & volatile

* Expert *

Join Date: Apr 2005

Location: 127.0.0.1

Posts: 2,171

Could you be more specific with the error. Does the query ignore the datareceive condition but comply with the dateend condition?

Have you verified that both dates are in a correct format before you insert them in the query?

__________________
Reading is the foundation for all knowledge - Unknown.

03-06-2011, 03:33 PM

Chris Allen Chris Allen is offline

Centurion

Join Date: Jul 2005

Posts: 167

Quote:

Originally Posted by cuber_killua

hi guys!!! can you please help with this query, it seems that this query doesnt read the begindate and only displays record with enddate.

Code:

str = ""
str = str & "Select sum(quantity) as totalpurchase from purchaseorder where itemcode='" & itmcode & "' and"
str = str & " category='Consumables' and company='" & company & "' and (datereceive >= '" & datebegin & "' and datereceive <= '" & dateend & "')"
str = str & "  and (postatus='received' or postatus='oh-hand')"

i am using vb2010 and mysql community server.

Try below, if you are using dates you need to use the # sign around them.

str = ""
str = str & "Select sum(quantity) as totalpurchase from purchaseorder where itemcode='" & itmcode & "' and"
str = str & " category='Consumables' and company='" & company & "' and (datereceive >= #" & datebegin & "# and datereceive <= #" & dateend & "#)"
str = str & " and (postatus='received' or postatus='oh-hand')"

__________________
If All Else Fails... Ask

03-07-2011, 06:14 PM

cuber_killua cuber_killua is offline

Contributor

Join Date: Sep 2004

Location: Philippines

Posts: 405

Thanks for the help.. its ok now guys.. Qua is right i forgot to format the date to match the date format of MySQL, i didnt notice that part... it should be;

Code:

format(datebegin, "yyyy-MM-dd")

same as well as dateend.. and another thing, MySQL doesnt use hash sign for date query..
thanks again..

__________________
Trion Towers and Woodsville Residences

The Radiance