problem with ascending date 1

[ProtegeV2]

Can someone please tell me why my records aren't sorting in ascending order? 02/09/2005 appears after 02/16/2005.

Here is my SQL - the report is grouped by "Date Range" - each value - group interval 1.

SELECT [SchedDate] & " - " & [EventEnd] AS [Date Range], tblSchedule.StartTime, tblSchedule.EndTime, tblSchedule.Organization, tblSchedule.[Event Name], tblSchedule.PLead, tblSchedule.ELead, tblSchedule.Donations, tblSchedule.[Community Location], DatePart("m",[Scheddate]) & "/" & DatePart("yyyy",[Scheddate]) AS WhatMonth, DatePart("w",[Scheddate]) AS WhatDay, tblSchedule.SchedDate, tblSchedule.Advisor, tblSchedule.[Other Community]
FROM tblSchedule
WHERE (((tblSchedule.SchedDate) Between [enter Event start date] And [Enter event end date]))
ORDER BY [SchedDate] & " - " & [EventEnd], tblSchedule.Organization, DatePart("m",[Scheddate]) & "/" & DatePart("d",[Scheddate]) & "/" & DatePart("yyyy",[Scheddate]);

Thanks very much.

 

[SkipVought]

Hi,

Are you sure that the values that are being sorted aren't
[tt]
2/16/2005 - someeventdate
2/9/2005 - someeventdate
[/tt]
cuz, [SchedDate] & " - " & [EventEnd] converts your dates to strings and "2/1" sorts before "2/9"

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[ProtegeV2]

The dates I mentioned are both from [SchedDate]. The list sorts correctly for January - three records in February aren't in proper ascending order for some reason.

The sorting and grouping on reports usually allows date fields to sort by "year" instead of "each value".

Can you remind me how to add ascending to the date range in ORDER BY in the SQL?

 

[PHV]

Have you tried simply this ?
ORDER BY tblSchedule.SchedDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Please COPY 'n' PASTE the query results for the rows that you just referred to - headings and all. I'd like to see it.

Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]

 

[ProtegeV2]

I ran the query with Start Date "1/1" and End Date "3/1":

Date Range
1/1/2005 - 1/31/2005
1/1/2005 - 1/31/2005
1/1/2005 - 1/31/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 2/22/2005
1/1/2005 - 3/20/2005
1/1/2005 - 3/20/2005
1/1/2005 - 3/20/2005
1/1/2005 - 3/20/2005
1/1/2005 - 3/20/2005
1/10/2005 - 2/4/2005
1/12/2005 -
1/12/2005 -
1/13/2005 -
1/14/2005 -
1/15/2005 -
1/17/2005 -
1/20/2005 -
1/20/2005 - 1/23/2005
1/20/2005 - 1/23/2005
1/20/2005 - 1/23/2005
1/22/2005 -
1/22/2005 -
1/22/2005 -
1/25/2005 -
1/28/2005 -
1/28/2005 - 1/30/2005
1/29/2005 -
1/29/2005 -
1/31/2005 - 2/4/2005
2/1/2005 -
2/1/2005 -
2/11/2005 -
2/12/2005 -
2/16/2005 -
2/19/2005 -
2/2/2005 - 2/6/2005
2/21/2005 -
2/22/2005 -
2/25/2005 -
2/25/2005 -
2/26/2005 -
2/26/2005 -
2/5/2005 -
2/9/2005 -
2/9/2005 -
3/1/2005 -

 

[PHV]

I hope you're now convinced ....
Replace this:
SELECT [SchedDate] & " - " & [EventEnd] AS [Date Range]
By this:
SELECT Format([SchedDate],'mm/dd/yyyy') & " - " & Format([EventEnd],'mm/dd/yyyy') AS [Date Range]

And this:
ORDER BY [SchedDate] & " - " & [EventEnd], tblSchedule.Organization, DatePart("m",[Scheddate]) & "/" & DatePart("d",[Scheddate]) & "/" & DatePart("yyyy",[Scheddate]);
By this:
ORDER BY 1, 4;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ProtegeV2]

I love you PHV! Thanks, it's perfect.

 

[SkipVought]

Do you understand WHY?

You changed your DATE to a STRING for the purpose of SORT and that's why
"2/19"
sorts before
"2/2/"




Skip,
[sub]
[red]Be advised:[/red] Researchers have found another Descartes trueism, "Cogito ergo spud."
"I think; therefore, I YAM! [/sub]