Report does not sort by date

[Thanks, Buddy]

I posted this under Access report - but I'm a newbie and all of the answers
were too technical.

I have a table with a date field where a person enters the date. When i
create a report and sort by date in the report =- it does not sort correctly.
It sorts this way: 12/10/09, 12/04/09, 12/09/09; It is reading 12/10/09 as
12/1/09.

If anyone can sort of walk me through this, it would be appreciated. I do
have access 2007 for dummies and can't find the answer there either.

Thanks

 

[Philip Herlihy]

Critical question - are you storing dates as a Date/Time field, or as a text
field? Sounds to me as if it's sorting on a text representation of the
date, rather than the underlying (numeric) representation that Access uses
for Dates (if you choose that).

Here's an article (Access 2003, but the principles haven't changed) which
explains how this works.
http://office.microsoft.com/en-us/access/HA011102181033.aspx
Read as far as seems relevant.

You could get the same (wrong) result if you stored the date "correctly",
but used in the report after converting it to a string using one of the many
functions which can be used to manipulate date values.

Phil, London

 

[Fred]

I though that too, although I don't think it would explain the observation
(if accurate) that it is interpreting a "01" in the middle of a string as a
"1"

Unless Microsoft "false intelligence" has invaded the middle of text
strings!

 

[KARL DEWEY]

I expect that the post was not exactly correct.

I think it actually was like this --
12/10/09, 12/4/09, 12/9/09

 

[Thanks, Buddy]

Yes, the data type for the field is date/time.

Also, the poster below was correct - it is sorting by 12/10/09, 12/4/09,
12/9/09 (not 12/10/09, 12/04/09, 12/09/09).

Thanks.

 

[Philip Herlihy]

That's a puzzle. I'm still inclined to think that Access is seeing an
alphanumeric string, and not a numeric value interpreted as a Date/Time
field.

To get to the report, the value concerned has to:
1) Reside in a table
2) Be extracted from the table by a query (usually) acting as the Record
Source of the Report
3) Be displayed in a control (often a text box) on the report itself

You've said that the field is a Date/Time value - I presume that's the table
definition.

Could you check how the value is retrieved in the query? If someone has
used the "Format()" function (not the same as changing the format settings)
then the result of that is a string. You could view the query in SQL mode
and post that text here.

One thing worth trying is copying your query (or creating a new one) and
adding this calculated field:
FieldType:VarType([MyDateField])
.... replacing MyDateField with the name of your "date" field. The value
returned (see Help on VarType) will tell you what Access thinks it's looking
at. 8 means it's a string.

Failing that, have a look at the properties of the control in which the Date
value is displayed. Any "extras"?

Phil