SQL sort problems

[B.Snell]

I have a text field in an Access database which has a series of dates (I have to use a text field rather than a 'date' field due to some records have a series of dates rather than a singe date (i.e., 01/12-14/2001, meaning Jan. 12th thru the 14th).

I'm trying to have ASPdb sort this field based on what today's date is. Here's the statement I'm using:

MyDb.dbSQL = "SELECT * from [2001] WHERE EventDate >= #"& Now() &"# ORDER BY EventDate ASC"

The problem I'm having is the database has a total of 77 records, of which only 5 are prior to today's date. However when I access this page, only records with dates beginning on 10/01/2001 thru 12/31/2001 are being displayed.

What's happening to all the records from 01/18/2001 through 09/30/2001? Does this have something to do with the manner in which the dates are entered (i.e., 01/12/2001 as opposed to 1/12/2001)?

Thanks,
--Bart Snell

[Frank Kwong]

Sounds like an SQL problem. Even consider divide the fields into From_date and To_Date. SOunds lik eou are sorting a text filed and expect dates results.
1/1/2001, 01/01/2001 ans 1/1/1 are the same.

FK


------------
B.Snell at 1/18/01 2:06:01 PM

I have a text field in an Access database which has a series of dates (I have to use a text field rather than a 'date' field due to some records have a series of dates rather than a singe date (i.e., 01/12-14/2001, meaning Jan. 12th thru the 14th).

I'm trying to have ASPdb sort this field based on what today's date is. Here's the statement I'm using:

MyDb.dbSQL = "SELECT * from [2001] WHERE EventDate >= #"& Now() &"# ORDER BY EventDate ASC"

The problem I'm having is the database has a total of 77 records, of which only 5 are prior to today's date. However when I access this page, only records with dates beginning on 10/01/2001 thru 12/31/2001 are being displayed.

What's happening to all the records from 01/18/2001 through 09/30/2001? Does this have something to do with the manner in which the dates are entered (i.e., 01/12/2001 as opposed to 1/12/2001)?

Thanks,
--Bart Snell