Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • 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
 
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
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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