QUERY QUESTION 1

[JENUTILITY]

I HAVE A QUESTION WITH A QUERY -
I MADE A QUERY TO FORMULATE A WEEK FOR MY DATES : WEEKFORMAT([DATE],"ww&quot) - THIS RETURNS A WEEK # FOR MY DATES, THIS WORKS OKAY
MY PROBLEM IS, WHEN I GET INTO MY NEXT QUERY I AM GROUPING ON WEEK # (IN ASCENDING ORDER) AND SUMMING A FEW OTHER FIELDS, THIS WORKS OKAY OTHER THAN MY WEEKS IN ORDER ARE SHOWING WEEK 10,11,9 ANY WAY I CHANGE IT THE WEEK 9 IS IN THE MIDDLE NOT THE FIRST RECORD? ANY HELP WOULD BE APPRECIATED. (THOSE ARE THE ONLY 3 WEEKS IN MY QUERY SO FAR)
THANK YOU

 

[Rohdem]

The query is treating the field week as a string, therefore it is comparing the first character in each of the fields and putting it in the order 1,1,9.

I would just concatenate a 0 onto strings that were of length one,

week: IIf((Len(Format([date],"ww&quot)=1),"0" & Format([date],"ww&quot,Format([date],"ww&quot)

but someone else might have a cleaner way of doing it. Mike Rohde
rohdem@marshallengines.com

 

[THoey]

Couldn't you use the CINT() function to convert it to a numeric, thus allowing the numbers to sort themselves?

:(CINT(FORMAT([DATE],"ww")))

Not sure if this is the best way or not... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[Rohdem]

Yeah, that does make more sense, doesn't it. Just need to know the fuctions. Mike Rohde
rohdem@marshallengines.com

 

[Rohdem]

I also need to learn how to spell.) Mike Rohde
rohdem@marshallengines.com