Query Order 1

[Soulbait]

I have a query set up currently that sorts by 2 fields

The first field is a 3 to 6 digit field and is the primary sort.

The second field is a 3 to 4 digit field and is secondary

For instance, the first field ID may be 27684 then the next field is say 031
well the next record in the sort will be 27684 and 032

Now there are on an average of 10 of these records with the same primary sort field in this case 27684 however, when i go to sort it looks like this

27684 031
27684 0310
27684 032
27684 033
27684 034
27684 035
27684 036
27684 037
27684 038
27684 039

However I would like it to stay in a more proper order for my scenario, in which case it would be.

27684 031
27684 032
27684 033
27684 034
27684 035
27684 036
27684 037
27684 038
27684 039
27684 0310

Is there anyway I can do this?

The lowest number in the secondary field would be most like 035 the highest number I've seen so far is 0317 but 75% of the time it stops at like 0310

We have to keep this format since the first 2 digits of 0310 are counted as the month and the last 2 digits are a rising integer.

Thanks for anyones help

Soulbait
"I'm a bomb technician, if you see me running try to keep up"

 

[scriverb]

Create a new column to be sorted on while using a Format function statement to force a right justification of the text value. The following SQL shows you how it should look:

SELECT tblYourTable.Field1, tblYourTable.Field2
FROM tblYourTable
ORDER BY tblYourTable.Field1, Format([tblYourTable]![Field22],"00000"
Bob Scriver

 

[Soulbait]

Thanks that worked out quite nicely.

Soulbait
"Despite the cost of living, it's still quite popular"

 

[scriverb]

Soulbait: Glad that I could help. Bob Scriver