By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,805 Members | 1,270 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

Problem Sorting Dates

P: 58
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:


instead of:


The problem then carries over to my report.

How do I make it so the report will come out like the second example?
Oct 26 '06 #1
Share this Question
Share on Google+
11 Replies

P: 3
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

Oct 26 '06 #2

Expert Mod 15k+
P: 31,470
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.
Oct 26 '06 #3

P: 58
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?
Oct 27 '06 #4

Expert Mod 15k+
P: 31,470
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.
Oct 27 '06 #5

P: 58
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!
Oct 27 '06 #6

Expert 100+
P: 1,418

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);

Oct 27 '06 #7

Expert Mod 15k+
P: 31,470
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.
Oct 27 '06 #8

P: 58
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?
Oct 28 '06 #9

Expert Mod 15k+
P: 31,470
If you remember, the [Date By Day] fields are both formatted into text strings.
Expand|Select|Wrap|Line Numbers
  1. 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 :-
Expand|Select|Wrap|Line Numbers
  1. ORDER BY CDate([tblDayInfo Query].[Date By Day]);
Oct 28 '06 #10

Expert 100+
P: 1,418
Yeah if the field isn't Date/Time type

This will help

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

Oct 28 '06 #11

P: 58
That worked. Thank you.
Oct 30 '06 #12

Post your reply

Sign in to post your reply or Sign up for a free account.