Access 2007 sorting problem

[Art Vandaley]

Hi,

In a query I want to sort a group of date from a table's date field starting
with 10 December 2006 and ending with 21 November 2007 by using only year
and month parts in different columns. I have following result:

Year______Month--------->(Ascending, Ascending)

2006______12
2007______1
2007______10
2007______11
2007______2
2007______3
2007______4
2007______5
2007______6
2007______7
2007______8
2007______9

Year : Format$(

.[DATE];'yyyy')
Month : Format$(

.[DATE];'m')


Why are 10 and 11 (October and november) not in order (at the bottom)?

Thanks alot for any help....

 

[Rick Brandt]

Hi,

In a query I want to sort a group of date from a table's date field
starting with 10 December 2006 and ending with 21 November 2007 by
using only year and month parts in different columns. I have
following result:
Year______Month--------->(Ascending, Ascending)

2006______12
2007______1
2007______10
2007______11
2007______2
2007______3
2007______4
2007______5
2007______6
2007______7
2007______8
2007______9

Year : Format$(

.[DATE];'yyyy')
Month : Format$(

.[DATE];'m')


Why are 10 and 11 (October and november) not in order (at the bottom)?

Thanks alot for any help....

The format funtion returns a string so the result sorts alphabetically, not
numerically. Either use...

Month : Format$(

.[DATE];'mm')

....so you get leading zeros on single digit months or convert to a number...

Month : CInt(Format$(

.[DATE];'m'))

 

[Geoff]

Hi,

In a query I want to sort a group of date from a table's date field
starting with 10 December 2006 and ending with 21 November 2007 by using
only year and month parts in different columns. I have following result:

Year______Month--------->(Ascending, Ascending)

2006______12
2007______1
2007______10
2007______11
2007______2
2007______3
2007______4
2007______5
2007______6
2007______7
2007______8
2007______9

Year : Format$(

.[DATE];'yyyy')
Month : Format$(

.[DATE];'m')


Why are 10 and 11 (October and november) not in order (at the bottom)?

Thanks alot for any help....

becuase they are stored as text, not dates ?