query string

[georgepatotk]

can anyone tell me what's wrong with the querystring below?

 

sqlquery = "SELECT * FROM tblBuys WHERE convert(varchar, BuyDate,103) = '" & Format(Date.Today, "dd/mm/yyyy") & "' ORDER BY BuyKey"

 

Thanks in advance.

[Mykre]

What error are you getting...

 

Also Have you tried adding the size of the varchar to be created.

sqlquery = "SELECT * FROM tblBuys WHERE convert(varchar(20), BuyDate,103) = '" & Format(Date.Today, "dd/mm/yyyy") & "' ORDER BY BuyKey"

 

can anyone tell me what's wrong with the querystring below?

 

sqlquery = "SELECT * FROM tblBuys WHERE convert(varchar, BuyDate,103) = '" & Format(Date.Today, "dd/mm/yyyy") & "' ORDER BY BuyKey"

 

Thanks in advance.

[PlausiblyDamp]

You may be better of parameterising the query or using a stored proc rather than converting the dates to strings and then doing string comparisons on them.

[georgepatotk]

I got the solution:

 

sqlquery = "SELECT * FROM tblBuys WHERE BuyDate= #" & Date.Today & "# ORDER BY BuyKey"

[Nerseus]

Also, make sure you mention the Database you're using as the formatting of dates is different for Access vs. SQL Server (for example).

 

I'm glad you removed the "CONVERT" from your date column. If it were already a date, there was no need to convert it. Plus, a convert to varchar would not only be slower (the DB would have to convert every row) but the compare would be slower (string based vs. date based).

 

The only thing I'd suggest adding is the use of ToString on the date:

sqlquery = "SELECT * FROM tblBuys WHERE BuyDate= #" & Date.Today.ToString("MM/dd/yyyy") & "# ORDER BY BuyKey"

 

-ner