Sorting by date.

[JGalletta]

I have a report sorted by date that will sort in this order

1/12/2012
1/2/2012
11/2/2012
2/13/2012

Its obviously sorting by the first digit instead of by logical date order.

How can I format the field so that it gets sorted properly? For example get the date to be displayed/stored as 01/02/2012 instead of 1/2/2012?

 

[plog]

It's not that the format is wrong, the field type is wrong. From what you described that field is text and not a date. I would make a copy of the database and then change the field type of that field to Date/Time. Format is irrelevant to this problem, but you can designate it in the Format area.

 

[JGalletta]

Well, the original data is in the correct format regarding its field type. I am however, sorting by a calculated value in a query:

REIMax: DMax("REI","qryAppRecord","[qryAppRecord]![Field] ='" & [Field] & "'")

Where REI is calculated using the following:

REI: DateAdd("h",[tblPesticides]![REI],[tblApplicationRecord]![Date of Application]+[tblApplicationRecord]![Time])

[tblPesticides]![REI] is a Number Type field. Is this where the problem is coming from? Changing this field to a Date/Time field yields data that is not easily read, for example 128 days shows up as 6/16/1900.

Maybe you can tell me where this value is losing its type as it is getting passed around through the program.

 

[NickHa]

... and to format the date when you have the Date/Time field, you need Format(datefield,"dd/mm/yyyy") to get it the way you want it in any display context. As plog says, this is independent of the actual data field content.

 

[JGalletta]

Ok thanks.