Solved

ASP, MS Access SQL string help!!

Posted on 2006-06-21
4
217 Views
Last Modified: 2011-09-20
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
Comment
Question by:lunchboxbill
  • 2
4 Comments
 
LVL 95

Expert Comment

by:Lee W, MVP
ID: 16952223
Just guessing - perhaps access is storing the dates in MM/DD/YYYY format?
0
 
LVL 1

Author Comment

by:lunchboxbill
ID: 16952252
how do i switch it around? do i have to do it in the asp or access side?

thanks
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 70 total points
ID: 16952260
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
 
LVL 43

Expert Comment

by:TimCottee
ID: 16952273
lunchboxbill,

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

Tim
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question