Date sorting problem

[tdfreeman]

Does anyone know why when I sort ascending on a date field the order shows up as follows:

1/1/2049
6/27/2002
6/27/2002
6/27/2002

1/1/2049 should be last. This is a date field (Short Date). Shouldn't Access know how to sort dates? I am using the design view of a query to do this sort.

Thanks in advance for your help.

Tammy

Thank you for your help.

Tammy

 

[jdegeorge]

Are you sure the field is DATE and not TEXT? If so, are you sorting ASCENDING or DESCENDING?

Jim DeGeorge

 

[tdfreeman]

Yes. The field is definitely short date. I am sorting Ascending. FYI, I sorted on different data and the field is sorting as follows:

1/3/2003
1/3/2003
1/3/2003
12/31/2049
12/31/2049
2/3/2003
2/3/2003

So, it looks like it is sorting as text, but that doesn't make sense for the field is date.

Note that the 12/31/2049 date was assigned to the field as a dummy field. It was assigned with the following VBA code.

rstCompliance![MinInvoiceDate] = #12/31/2049#

Then I take the table that this field is in and I sort the table. First it sorts on Active and non-active. If it is active it is supposed to sort on MinInvoiceDate. The query field that I created to sort on MinInvoiceDate is as follows:

NestedActiveSort: IIf(Status="Active",MinInvoiceDate,"&quot

It is sorting ascending on NestedActiveSort. MinInvoiceDate is a date field.

Any ideas why it is sorting this way?

Thanks again.

Tammy



Thank you for your help.

Tammy

 

[tdfreeman]

Thanks for your help, but I solved the problem. Where I was sorting on:

NestedActiveSort: IIf(Status="Active",MinInvoiceDate,"&quot

you will notice that in the false clause of the if statement I have a empty string. Well an empty string is a text field and, therefore, NestedActiveSort was formatting as a text field.

When I changed "" to #1/1/2100#, the data sorted correctly.

Thanks again for your help.

Tammy

Thank you for your help.

Tammy

 

[jdegeorge]

I noticed that you don't have the leading zeros in your date format. I changed my short date on my PC's control panel to MM/DD/YYYY and dates always sort correctly for me. Try that, but you have to have your users do the same as it's a PC setting that's not controlled in Access.

Jim DeGeorge