Selecting Date Range using SQL from MS Access...

[martindavey]

Hi Please help, I'm going MAD...

I'm having trouble saving data that contains a date and then correctly retreiving data that falls within a specified range of dates.

The data on myTable is as follows:

05/12/01
05/15/01
01/05/19
01/05/20
01/05/21
01/05/26

All of the dates are may 2001 (I don't know why the dates are not stored in a consistant format?! My insert SQL is at the bottom, maybe you know why?).

FYI. The following selects retreive the rows shown below them, all but the last select which doesn't work:

1. SQL = "select * from myTable " &_
"where myDate >= #12/5/01# " &_
&quot;and myDate <= #17/6/01# &quot;

Retreives:
05/12/01
05/15/01

2. SQL = &quot;select * from myTable &quot; &_
&quot;where myDate >= #19/5/01# &quot; &_
&quot;and myDate <= #25/5/01# &quot;

Retreives:
01/05/19
01/05/20
01/05/21

3. SQL = &quot;select * from myTable &quot; &_
&quot;where myDate >= #26/5/01# &quot; &_
&quot;and myDate <= #1/6/01# &quot;

This returns nothing.


My Insert is:

someDate = Date - 1
theDate = FormatDateString( someDate )

SQL = &quot;insert into myTable ( myDate ) &quot; &_
&quot;values ( #&quot; + CStr(theDate) + &quot;# ) &quot;


NB.
1. I have the following session variable declared:
<% Session.LCID = 2057 ' English Locale %>

2. My regional short date style is d/M/yy

3. The column myDate is a declared as a date on my DB, I haven't specified the format and it's therefore defaulted to short date - which shows up as dd/mm/yy in MS Access.