Comparing Dates in SQL

[Ovatvvon]

Hello,
this seems like it should be a simple problem, but I'm havin some problems with it. USing Access 2k with my ASP.

I'm making a search page for records in the database. One part of it is to search for records before a date, after a date, or between two dates.

The problem is that even though there are dates in the database that are between the two selected dates from the form, no results are found.

Here are two different SQL statements I've tried using...

sql = &quot;SELECT * FROM rec WHERE (((rec.dateSubmitted) > &quot; & CDate(YearStart & &quot;-&quot; & MonthStart & &quot;-&quot; & DayStart) & &quot AND ((rec.dateSubmitted) < &quot; & CDate(YearEnd & &quot;-&quot; & MonthEnd & &quot;-&quot; & DayEnd) & &quot)&quot;


sql = SELECT * FROM rec WHERE ((rec.thedate) BETWEEN &quot; & CDate(YearStart & &quot;-&quot; & MonthStart & &quot;-&quot; & DayStart) & &quot; AND &quot; & CDate(YearEnd & &quot;-&quot; & MonthEnd & &quot;-&quot; & DayEnd) & &quot&quot;


It doesn't come up with any errors for either one, it just doesn't return results when there should be.

Am I doing somthing wrong here that someone else can see?
-Ovatvvon :-Q

 

[vestrinang]

Try this:
sqlString = &quot;SELECT * FROM rec WHERE rec.TheDate BETWEEN #&quot; & Month(myBegDate) & &quot;/&quot; & Day(myBegDate) & &quot;/&quot; & Year(myBegDate) & &quot;# AND #&quot; & Month(myEndDate) & &quot;/&quot; & Day(myEndDate) & &quot;/&quot; & Year(myEndDate) & &quot;#;&quot;
myBegDate and myEndDate are of type Date.

If you are still having problems, please let me know.

 

[fhlee]

Hi,
I think the problem is in the format of your date. Your code provides yy-mm-dd. The system setting is usually mm-dd-yy. The format you use should be in accordance to the Regional Setting of your PC/Server where the code runs.
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
Visit -->

http://www.replicaSi.com

for (Replica) Watches, Pen, Handbags, Hats, Jerseys and more ... at prices that makes your code spin ...
~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[Ovatvvon]

vestrinang,
that worked perfectly. I remember that Access needs # for dates...at least now that you mention it. Can't believe I forgot.

Thanks for your help!

-Ovatvvon :-Q