Report won't sort by date when query contains date formatting

[choaspes]

Hi All

I wonder if anyone can shed some light on this maddeningly nonsensical facet of Access 2010?

A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.

However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July.

WHY? :banghead:

 

[MarkK]

Because the Format() function returns a string. This is well documented in the first line of the Format() function help item . . .

Format Function
Returns a Variant (String) containing an expression formatted according to instructions contained in a format expression.

To remove the time from a date and still return a date variable, use the DateValue() function.
hth

 

[choaspes]

Thank you, I'll try that out when I get home.

I sense frustration in your "..."s. My apologies, the hardest bit about learning access for me is working out when these different data types are actually important. As demonstrated above my report doesn't give a fig about thinking a date is text and but still sorting it as if it's a date. If it were consistent in its pickyness I would probably pick up some of the fundamentals more quickly.

 

[MarkK]

No, I'm not frustrated and I apologize if that came across that way. That's an easy thing to overlook.

 

[SOS]

Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text,

Just something to take note of. The sort A to Z or Z to A does not infer that it is text. That is the standard way of saying from lowest to highest or highest to lowest. It means that if it is a number A to Z will sort from the lowest number to the highest. If it is a date it will sort from the earliest date to the most recent. Z to A reverses those. Numbers will sort from highest to lowest and dates from the most recent to the earliest.

 

[choaspes]

Thanks again for your help, I would have been going round in circles for ages if you hadn't cleared that up. May I trouble you for one more piece of advice? I fear it's going to out me as a complete idiot at this stuff.

Why does it matter in this scenario that the date format is a string when it doesn't seem to in others? For example I've just been given some advice on the "forms" part of the forum about setting date formats for text boxes using an afterupdate event in which I do use "format". Is the difference that on the form I am writing to a field which is already set to the date/time date type, whereas in the query there is no predefined format for the expression?

Another example would be an archive VBA routine I run that seems to work perfectly well: I Dim a bunch of fields as Vars in the current record, create a new record and define the fields in the new records as those Vars - at no point do I define any of those Vars as dates - yet it seems to work fine, is this again because the data types for the fields are already set?

 

[choaspes]

Thank you SOS that's very helpful too.

 

[SOS]

Why does it matter in this scenario that the date format is a string when it doesn't seem to in others?

The Format function turns the values to Text. And Text sorts differently than numbers.

Numbers will go (A to Z):
1
22
38
46
546
1156

But Text sorts on the first character and then the next, etc. So that set of numbers would be

1
1156
22
38
46
546

So if there is a date which is really text, then it will sort

3/1/2013
3/13/2013
10/15/2013

as

10/15/2013
3/1/2013
3/13/2013


Does that help?

 

[Brianwarnock]

Choaspes you are now asking the same question on 2 threads, that is annoying and unfair to posters who are taking the time to help you.

Brian

 

[choaspes]

Thank you, yes that does help.

I suppose the thing I'm still a bit unclear on is how Access decides the format of the expression in the union query when some of the queries within that union query are generating a date format and some are generating text/string format.

The report is grouped into queries and even for those groups in which I haven't used Format() the sort is still sorting as if I've given text values as you describe.

 

[SOS]

If one of the fields in a Union query is text and the same field in another query within that Union query is a date, it will treat it as text. It always converts non-like fields (in the same spot in the field order) to text.

So, for example, if I have a query like:

Select Format(MyField, "mm/dd/yyyy") As MyDate, FieldX, FieldY, FieldZ

And Field X is a date but the first one as MyDate is now text. if, in the union you had

Select Format(MyField, "mm/dd/yyyy") As MyDate, FieldX, FieldY, FieldZ
Union
Select FieldW, FieldX, FieldY, FieldZ


FieldW falls in the same location as MyDate in the other Select, so FieldW would be treated as Text like MyDate.

 

[choaspes]

My apologies Brian, the same question happened to arise from two distinct questions that did not belong in the same place. I was not aware when I asked my first two questions that the follow up question to both would be the same, and it seems to be just as rude of me to arbitrarily decide which of the people who were kidn enough to respond to my initial question not to ask the follow up to. I'm sorry if that's generated annoyance but I don't really see an entirely annoyance-free way to proceed under the circumstances. Perhaps a third thread in a different place? But where? The question related to both forms and reports.

 

[choaspes]

Thank you SOS that's cleared it all up.

This is a marvellous place and you are wonderful people, I'm sorry to have been annoying and unfair in the course of my travails.

 

[Brianwarnock]

My apologies Brian, the same question happened to arise from two distinct questions that did not belong in the same place. I was not aware when I asked my first two questions that the follow up question to both would be the same, and it seems to be just as rude of me to arbitrarily decide which of the people who were kidn enough to respond to my initial question not to ask the follow up to. I'm sorry if that's generated annoyance but I don't really see an entirely annoyance-free way to proceed under the circumstances. Perhaps a third thread in a different place? But where? The question related to both forms and reports.

Post a link explaining what is happening, people can then cross reference

http://www.access-programmers.co.uk/forums/showthread.php?p=1269223#post1269223

Would take you to the other thread.

Brian

 

[choaspes]

In a new thread?

 

[Brianwarnock]

No, continue in one thread but in the other post a link and say that you have a question concerning date time formatting that arises because of the two threads and would welcome help.

I admit that your situation was a bit tricky but you will get a more complete answer in one place and avoid duplicate effort by others.
No real harm done this time but I've seen duplicate threads answered by many before it is realised.

Cheers

Brian

 

[choaspes]

Thanks Brian, and thanks again everyone for the advice.

 

[Pat Hartman]

As demonstrated above my report doesn't give a fig about thinking a date is text and but still sorting it as if it's a date. If it were consistent in its pickyness I would probably pick up some of the fundamentals more quickly.

Access is consistant. I think if you examine your report, you will see that it is sorting on an unformatted date field.

Dates are stored internally as double precision numbers with the integer being the number of days since Dec 30, 1899 and the decimal is a fraction of a day representing the number of minutes. Because dates are actually numbers, Access (SQL Server, DB2, Oracle, and even Excel do the same thing but with different origin dates) sorts a field defined as a datetime data type in actual date order. Once you format a date into a string, it sorts as a string would sort as in the example posted by SOS.

print cdbl(Now())
41448.0186805556
print now()
06/23/13 12:26:59 AM