Inserting/Selecting date ranges (using ASP/MS Access)

[martindavey]

Hi Please help, I'm going MAD...
(I've put this mail on the ASP board also, but perhaps you may know more about Access date storage).

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.

 

[CFtaxtron]

The third select statement is not returning anything because their is no data to be returned through those two dates. Its more then likely throwing an error as you put in the month as 26. You need to fix your data so that its a consistent format, right now it has

05/12/01 - May 12, 2001
05/15/01 - May 15, 2001
01/05/19 - Jan 5, 2019
01/05/20 - Jan 5, 2020
01/05/21 - Jan 5, 2021
01/05/26 - Jan 5, 2026

I assume you want the data to look like this

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

Tim

 

[martindavey]

I agree that consistent dates is necessary, it doesn't however explain any of my problems here.

Firstly, the dates I want are LCID 2057, English, England d/M/yy - So my data should be:

12/05/01
15/05/01
19/05/01
20/05/01
21/05/01
26/05/01 (All in May 2001)

Secondly, why doesn't the third select return anything when the second select does ??

Thirdly, why has my data been inserted as y/m/d in Access in the first place with the insert sql I used? LCID 2057 is England isn't it?

Thanks for you response