querydef (Access)

Topic

New Reply

I am using the following statement to construct a query

strSQL = “select * from qryProductionSales where tdate >= #” & txtStart & “# and tdate = #01/01/01# and tdate =#01/01/01# And <=#30/01/12#

which causes no record in the query result. My computer regional setting for the date is yy/mm/dd.

I wonder what causes the problem (changing the date format and no record in the query result).

Thanks

Reply | Quote

Replies

I think you will find that Access expects the date in the where clause in USA format regardless of the regional settings. I use UK dates and I always have trouble getting it right.

From experience I would suggest that you test your where clause with test dates like 01/01/01 and 02/02/02. It doesn

Reply | Quote

Rupert is right. You need to pass the date in US format to the query. Try :

strSQL = “Select * fromqryProductionSales Where tdate >= #” & Format(txtStart, “mm/dd/yyyy”) & “# and tdate <=#" & Format(txtEnd, "mm/dd/yyyy") & "#"

Ryan

Reply | Quote

Thanks guys,

I also find out that if I use the datevalue function (instead of format), it works fine.

Reply | Quote