Sort not working for calculated week number

[st4rcutter]

This is a new one for me. I have a database that calculates the week number based on one of two fields. Here is the expression:

Week: Format(IIf([PM Tracking]![Sup FOC] Is Null,[PM Tracking]![Original FOC],[PM Tracking]![Sup FOC]),"ww")

Field names [Original FOC] and [Sup FOC]are short date values of (6/01/2006) format. Everything works correctly but the results do not sort by week correctly. For the most part the week is sorted but there are some values such as week 7 and week 9 that occur after week numbers 10 through 49. Does the sort reference back to the orginal data? I suspect that is the culprit.

 

[Hakala]

Hi, st4rcutter!

I've had that problem within tables, because the system seems to sort by first digit, then next digit, etc. So my list of 12 might come out 1, 10, 11, 12, 2, 3, 4, 5, 6, 7, 8, 9. I had to add a zero (or two, if using items which go into the hundreds) in front to stop it (01, 02, etc).

I'm not sure how to control that in the SQL.

 

[rjoubert]

Try wrapping a CInt around your expression.

 

[PHV]

Week: Val(Format(Nz([PM Tracking]![Sup FOC],[PM Tracking]![Original FOC]),"ww"))


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

 

[st4rcutter]

Thanks PHV, That did the trick. I did try using CInt but recieved an error regarding invalid data type.