Dates do not sort correctly

Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

Then you are storing a string that looks like a date or you have applied the
format function to a datetime field and that converted the date into a string.

You can use the DateValue function to convert the string into a datetime type
and then sort on that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks wrote:
Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

Apparently your date value is actually a string/text. Are you sorting by an
actual date field or are you sorting on a formatted date? If you have
formatted the date field in your report's record source, don't. Leave the
formatting to the control in the report where you display the date.

--
Duane Hookom
Microsoft Access MVP


"Thanks, Buddy" wrote:

Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

Thanks Duane, The field in my source table is a date field that the user
enters. I can only format it as a date. When I sort the date in the report,
Access recognizes it as a date field, but does not sort it as a date field.

Thanks,
Buddy

"Duane Hookom" wrote:

Apparently your date value is actually a string/text. Are you sorting by an
actual date field or are you sorting on a formatted date? If you have
formatted the date field in your report's record source, don't. Leave the
formatting to the control in the report where you display the date.

--
Duane Hookom
Microsoft Access MVP


"Thanks, Buddy" wrote:

Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

Formating creates a string - text. Use the DateTime field unformated for
sorting and display your formated one in report.

--
Build a little, test a little.


"Thanks, Buddy" wrote:

Thanks Duane, The field in my source table is a date field that the user
enters. I can only format it as a date. When I sort the date in the report,
Access recognizes it as a date field, but does not sort it as a date field.

Thanks,
Buddy

"Duane Hookom" wrote:

Apparently your date value is actually a string/text. Are you sorting by an
actual date field or are you sorting on a formatted date? If you have
formatted the date field in your report's record source, don't. Leave the
formatting to the control in the report where you display the date.

--
Duane Hookom
Microsoft Access MVP


"Thanks, Buddy" wrote:

Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

Hey everyone, thanks for your help...but maybe I should post this under
newbie....I don't really know how to do what you are suggesting. I looked up
format type...and it only offers me different type of date formats.

"John Spencer" wrote:

Then you are storing a string that looks like a date or you have applied the
format function to a datetime field and that converted the date into a string.

You can use the DateValue function to convert the string into a datetime type
and then sort on that.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thanks wrote:
Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy
.

Three "mature" sages have all suggested you either have a text/string date
value or have somehow converted it to a test/string value.

Can you share the SQL view of your report's record source?
I assume you realize the sorting in the record source has little or no
affect on the sorting in the report.

--
Duane Hookom
Microsoft Access MVP


"KARL DEWEY" wrote:

Formating creates a string - text. Use the DateTime field unformated for
sorting and display your formated one in report.

--
Build a little, test a little.


"Thanks, Buddy" wrote:

Thanks Duane, The field in my source table is a date field that the user
enters. I can only format it as a date. When I sort the date in the report,
Access recognizes it as a date field, but does not sort it as a date field.

Thanks,
Buddy

"Duane Hookom" wrote:

Apparently your date value is actually a string/text. Are you sorting by an
actual date field or are you sorting on a formatted date? If you have
formatted the date field in your report's record source, don't. Leave the
formatting to the control in the report where you display the date.

--
Duane Hookom
Microsoft Access MVP


"Thanks, Buddy" wrote:

Hi All,

I'm Running Microsoft Access 2007.

In reports, when I sort my report by date - access does not sort it
correctly (i.e. it sorts dates in this order 12/10/09, 12/4/09, 12/9/09 (it
is reading 12/10/09 as 12/01/09).

Any clues?

Thanks,
Buddy

=?Utf-8?B?S0FSTCBERVdFWQ==?=
wrote in :

Formating creates a string - text. Use the DateTime field
unformated for sorting and display your formated one in report.

In fact, don't ever format for display values in the SQL
Recordsource -- that's a display issue and belongs in the
presentation layer, and not in the data retrieval layer.

The only exception to that would be in a form where you might want
to filter/sort on the formatted value. This would not apply to
dates, but it could apply to other values that you might be using an
expression for (e.g., and Nz() wrapper).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/