Solved

ASP, MS Access SQL string help!!

Posted on 2006-06-21
4
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 96

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:   The Exchange of informatio…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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