Problem Sorting Dates

[BurtonBach]

I made a query where the first column pulls a date field. I want the column in the query to sort by ascending date. I have confirmed that the format of the field in the actual table is set as 'Short Date.' However, when the query is executed I get:

10/12/2006
9/6/2006
9/7/2006

instead of:

9/6/2006
9/7/2006
10/12/2006

The problem then carries over to my report.

How do I make it so the report will come out like the second example?

[EdDale]

Open the report in Design View, Under the View Tab select Sorting and Grouping.
Put the name of your date field under Filed/Expression
Select Ascending under Sort Order

Hope this helps

Dale

[NeoPa]

Firstly, ensure that the query outputs the Date data in a Date format.
Secondly, organise the sorting for the report via 'Sorting and Grouping' within the Report design.

If you post your SQL we can see if there are any obvious problems.

[BurtonBach]

Thank you for both of your replies. However, I am still not getting the output I need. I believe the commend about the query outputing in the correct date format is where the problem lies. You said that the SQL might help.

I am actually working with a nested query. The first query is:

SELECT DISTINCTROW Format$(tblDayInfo.Date,'Short Date') AS [Date By Day], Sum(tblDayInfo.TotalHours) AS [Sum Of TotalHours], Count(*) AS [Count Of tblDayInfo]
FROM tblDayInfo
GROUP BY Format$(tblDayInfo.Date,'Short Date')
ORDER BY Format$(tblDayInfo.Date,'Short Date');

And the second query is (this one feeds the report I am needing to sort:

SELECT [tblDayInfo Query].[Date By Day], [tblDayInfo Query].[Sum Of TotalHours], tblTotalofTicketsDay.[Sum Of TicketQty], tblTotalofTicketsDay.[Count Of tblTruckingDtl], [tblDayInfo Query].[Count Of tblDayInfo], [Sum Of TotalHours]/[Count Of tblTruckingDtl] AS [Hr/Ld], [Sum Of TicketQty]*7.75 AS DaysTot, [DaysTot]/[Sum Of TotalHours] AS [$/Hr]
FROM [tblDayInfo Query] INNER JOIN tblTotalofTicketsDay ON [tblDayInfo Query].[Date By Day] = tblTotalofTicketsDay.[Date By Day]
ORDER BY [tblDayInfo Query].[Date By Day];

I am noticing now that there is no 'Short Date' at the end of the ORDER BY line of the second query. Is this my problem?

[NeoPa]

Just leaving for home....
But noticed two things.
1. ORDER BY should be before GROUP BY (I'm pretty sure)
2. You're using the Format$() function which will return a string.

Sorting by the string will give you an alphanumeric sort.
You can SELECT the format() (avoid using Format$) of the date but better to GROUP & ORDER BY the date in date format.
...Of course, if you SELECT the Format() - you will still have the problem in the report, so best not to use Format() at all in the query.

[BurtonBach]

I attempted to reverse the order on the Group By and Order By and got an error.

I also attempted to remove the Format as you suggested, however, I believe I need to replace it with something else in order to get it to work. Do you have any suggestions. I didn't intentionally put the Format into it. I created the queries in design view and did want them to show up as dates.

Could I beg more help with how to replace the Format syntax?

Thanks in advance!

[PEB]

Hi,

Do not use the Format function only in ORDER BY clause

Juste like:

ORDER BY tblDayInfo.Date;

And if tblDayInfo.Date has a Date/Time format your report will function correctly

If it's a text so the situation is different and You should use a conversion function Cvdate():

ORDER BY Cvdate(tblDayInfo.Date);

:)

[NeoPa]

How did I get the WHERE & ORDER BY clause positions mixed up?
You may well ask - My bad.

Now, I've just seen that PEB has snuck in there with an answer which is pretty well what you need.
The Format() (I'd still use Format() rather than Format$() mind you) of the data is required as it has to match exactly the [Date By Day] field in [tblTotalofTicketsDay].
The GROUP BY clause should really keep the Format() too but the ORDER BY clause should refer to the naked date before any manipulation.
The GROUP BY really only needs the Format() if it stores times as well as the basic date, but it is more thorough code to keep it anyway.

[BurtonBach]

Thank you much. It worked well. Is there any reason, though, that the correction didn't carry into the second part of the nested query? What I mean is, now I am getting the correct sort at the end of the first query, but not at the end of the second or in the report?

[NeoPa]

If you remember, the [Date By Day] fields are both formatted into text strings.
Therefore,

ORDER BY [tblDayInfo Query].[Date By Day];

at the end, sorts it by the text string which looks like a date.
I suppose I should add that, to sort by date here the last line should be :-

ORDER BY CDate([tblDayInfo Query].[Date By Day]);

[PEB]

Yeah if the field isn't Date/Time type

This will help

ORDER BY CVDate([tblDayInfo Query].[Date By Day]); AS last line

:)

[BurtonBach]

That worked. Thank you.