Sorting a .mdb database by date?

[Entropy1024]

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.

[K.Vanlalliana]

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.

[Entropy1024]

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

[debasisdas]

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

[Entropy1024]

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

[debasisdas]

you can do that in acess.

[K.Vanlalliana]

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

[jireh]

Maybe the date column was already sorted...

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

[Entropy1024]

Maybe the date column was already sorted...

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

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

[jireh]

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.

[Entropy1024]

Ah. OK thank you. I will do that.

Cheers

[Entropy1024]

Great, thank you, That fixed it. Sort of anyway ;)

It initially appears to have done nothing, but if I then close my program and restart it then the records are in date order. Is there some way to force a sort at runtime?

Many thanks for the help.

[debasisdas]

you need to convert the data in text format to date format and then sort.

[jireh]

In your code put this query before you bind it in the grid...

"select * from [tablename] order by datefield asc"

[Entropy1024]

OK great thanks got it to work. The following code worked.

AdHocData.RecordSource = "select * from AdHocData order by StartDateTime asc"