Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

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!
0
lunchboxbill
Asked:
lunchboxbill
  • 2
1 Solution
 
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
 
TimCotteeCommented:
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
 
TimCotteeCommented:
lunchboxbill,

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

Tim
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now