ASP, MS Access SQL string help!!

Hi,

sorry its only worth 70 points - its all i have! will bump iut up as much as i can when i accept the answer!

Can anybody tell me why this doesnt work?

sql = "SELECT * FROM table WHERE (myDate BETWEEN #"&date_1&"# AND #"&date_2&"#);"

the ouput sql is

SELECT * FROM productionSheet WHERE myDate BETWEEN #27/03/2006# AND #01/07/2006#;



the asp page doesnt crash out, but doesnt retrieve any of the data i am expecting it too.

the myDate field in the access db is a date/time datatype and is of a general format.

many thanks!
LVL 1
lunchboxbillAsked:
Who is Participating?
 
TimCotteeHead of Software ServicesCommented:
Hi lunchboxbill,

leew is kind of right. It is a known limitation of access that it will assume dates are in mm/dd unless that is obviously silly and has to be dd/mm (such as 27/03) when used with ADO or any other data access method outside of access itself, regardless of the locale settings on the machine(s) in question. The only way to safely query by date is to ensure that they are in an unambiguous format such as yyyy-mm-dd.

Tim Cottee
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Just guessing - perhaps access is storing the dates in MM/DD/YYYY format?
0
 
lunchboxbillAuthor Commented:
how do i switch it around? do i have to do it in the asp or access side?

thanks
0
 
TimCotteeHead of Software ServicesCommented:
lunchboxbill,

So yes you would have to do this on the asp side.

Tim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.