Format week 2

[pgmr]

Hi,
I am sorting my query based on field Week.
Week: Format$([Query2.Photo ETA1],"ww",0,0) where
Query 2.Photo ETA1 is a date.
I need to add a zero before the weeks 2 thru 9, so it sorts correctly. Currently it is sorting week 1, then
week 10 thru week 53, then week 2 thru 9.
It must be comparing the first position.
Any ideas?

 

[Golom]

Format$" returns a string so it's using a string sort rather than a numeric sort. Try an ORDER BY Clause like
[tt]
ORDER BY Val(Format$([Query2.Photo ETA1],"ww",0,0))
[/tt]

 

[scriverb]

You just change the text week value to an integer and perform the sort:

Week: CIng(Format$([Query2.Photo ETA1],"ww",0,0))

This will display as a numeric field and sort properly. If you really want the weeks to show as 01, 02, 03, etc then use the following:

Week: Format(Format$([Query2.Photo ETA1],"ww",0,0),"00")

Post back if you have any questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pgmr]

Thank You, Both posts were very helpful!!