Sort on Date

[Craig Hummel]

I have a query that reads as follows:
One of the fields is an expression from a form control that returns the date
in month and year.

Field 1
Field 2
Exp1: datepart("m",[datecontrol]) & "/" & datepart("yyyy",[datecontrol])



I need to sort on the expression but when I do it sorts wrong putting
10/2008 ahead of 7/2008.

I have tried tricks such as formating the expression with formatdatetime()
(Which returns a short date on the first of the month) but it still will not
sort on the expression.

Any help appreciated.

Craig

 

[Rick Brandt]

I have a query that reads as follows: One of the fields is an
expression from a form control that returns the date in month and year.

Field 1
Field 2
Exp1: datepart("m",[datecontrol]) & "/" & datepart("yyyy",[datecontrol])



I need to sort on the expression but when I do it sorts wrong putting
10/2008 ahead of 7/2008.

I have tried tricks such as formating the expression with
formatdatetime() (Which returns a short date on the first of the month)
but it still will not sort on the expression.

Any help appreciated.

Craig

As soon as you use that expression you no longer have a date. You have a
string and thus it will sort as a string. Add your date field a second
time un-altered and sort on that. You can uncheck the box so this field
does not appear in the query's output.