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

 

[KenSheridan via AccessMonster.com]

As you see, the consensus in both your other thread and here is that your
'date' is probably a text data type not a true date/time data type. To check
this open the table in design view and see what it says for the field in the
Data Type column. It should say 'Date/time', but if says 'Text' then that's
the cause of the problem. Whichever it is we need to know the answer to this
question before we can point you to a solution.

Ken Sheridan
Stafford, England

 

[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

 

[KenSheridan via AccessMonster.com]

Weird. The date/time data type in Access is actually implemented as a 64
floating point number, with the integer part representing each day and the
fractional part the times of day. It starts counting from 30 December 1899
00:00:00, which has an underlying value of zero. What you see is just a
formatted representation of the underlying number, and it can be formatted
however you wish, but the underlying value, which is what it is sorted by,
remains the same regardless of the format.

As I type this the value returned by the Now() function is, for me:

17/12/2009 17:39:27

For you it would be:

12/17/2009 17:39:27

but the underlying value is the same in both cases

40164.7357291667

which can be seen with: CDec(Now()).

So what Access is sorting by when it sorts a date/time value is just a number
which increases, day by day, second by second, as time advances. So an
earlier date will always sort before a later one regardless of the format in
which its expressed. Why this appears not to be happening in your case is a
mystery.

What is the report's RecordSource property. Is it the table itself? Is it
query? If the latter, what is the SQL of the query? Whatever the case, in
the report's sorting and grouping dialogue, is it sorted on the field name
itself, or on an expression which includes it? Finally, is the date the
first group level in the sorting and grouping dialogue?

Ken Sheridan
Stafford, England

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.

As you see, the consensus in both your other thread and here is that your
'date' is probably a text data type not a true date/time data type. To check

[quoted text clipped - 18 lines]

 

[Vicki B]

Sorting and Grouping in a report overrides the sorting in the underlying query. I wonder if this is the problem?

I posted this under Access report - but I am 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 cannot find the answer there either.

Thanks

On Wednesday, December 16, 2009 3:17 PM Philip Herlihy wrote:
Critical question - are you storing dates as a Date/Time field, or as a text
field? Sounds to me as if it is sorting on a text representation of the
date, rather than the underlying (numeric) representation that Access uses
for Dates (if you choose that).

Here is an article (Access 2003, but the principles have not 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

On Thursday, December 17, 2009 12:56 PM Philip Herlihy wrote:
That's a puzzle. I am 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 is 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 is looking
at. 8 means it is a string.

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

Phil