Dates do not sort correctly

  • Thread starter Thread starter Thanks, Buddy
  • Start date Start date
T

Thanks, Buddy

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
 
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.
 
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
 
Formating creates a string - text. Use the DateTime field unformated for
sorting and display your formated one in report.
 
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.
 
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.
 
Formating creates a string - text. Use the DateTime field
unformated for sorting and display your formated one in report.
Click to expand...

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).
 
Back
Top