Sorting a .mdb database by date?
 

I have a .mdb database that contains various records. One of the fields is date, ie '19/10/2008 00:00'.
How do I sort the database by date?

I an a beginner and currently learning with the 'Visual Basic in easy steps' by Tim Anderson. On page 104 he has an example database called 'Members'. To sort by surname he says to do the following:-

RecordSet = 1-Dynaset
RecordSource = Select * from Members Order By LastName

This then sorts the 'Members' database by Surnames.

So I would have thought that if I set my database 'AdHocData' to:
RecordSet = 1-Dynaset
RecordSourse = Select * from AdHocData Order By Date

this would re-arrange the database by date, but it appears to have no effect.

Can anyone please tell me where I am going wrong?

Many thanks.

Re: Sorting a .mdb database by date?
 

Hi,

if Your date column name is mdate, you have to slect the data as

RecordSourse = "Select * from AdHocData Order By mdate"

This would surely select the data as the order of date. If not correct for your computer, ssend your data to the forum and let me check.

Re: Sorting a .mdb database by date?
 

The date field is StartDateTime and I have added:-

Select * from AdHocData Order By StartDateTime

To the RecordSource line but it still does not sort via date order.

Please find attached the database.

Many thanks

Re: Sorting a .mdb database by date?
 

donot use DATE as a field name ,that is a datatype in database .

Re: Sorting a .mdb database by date?
 

So it won't work because I used the field name StartDateTime?

I can understand it not liking just 'Date' but find it extremely odd that it does not like the above.

Is it possible to rename my StartDateTime filed to something else, say 'SDT' without it erasing all the data in the database?

Cheers

Re: Sorting a .mdb database by date?
 

you can do that in acess.

Re: Sorting a .mdb database by date?
 

Hi,

I used select text for the date filed so that the date can always be displayed with the format you like / exspected and that is not effected by date format selected in the control pannel. For the order of date, please make another field like 'dval' in which the value of date will be store like this:

2-March-2008 is stored as 20080302

This can be done by using formatting code Format(2-march-2008,"yyyymmdd")



KVL

Re: Sorting a .mdb database by date?
 

Maybe the date column was already sorted...

Anyway, what type is your date declared? is it a date or text?

Re: Sorting a .mdb database by date?
 

The date is stored as a text field in the database.

Re: Sorting a .mdb database by date?
 

I see that's why your sorting of date doesn't work because it sorts out according to string or text... please change the data type to date or datetime.