OrderBy question

[Guest]

Hello,

Right now I have Subform that I am controlling how it Sorts by using the
"OrderBy" Property. It is working fine execpt when I try to Sort by a Field
in the table called "ProposalNumber" which is a Text Field. So when Sorted
by this Field you get:

"05-101"
"05-102"
"05-2"

Now I know it is Sorting like this becuase it is a Text Field but in reality
I would need it to be

"05-2"
"05-101"
"05-102"

How I can I get this to Sort Numerically rather than like a text field ?
And the problem is I cant get rid of the "-" becuase it is required so I
still have to leave the field as a Text field.

Any help would be greatly appreciated.

Thank you,
Jeff

 

[Allen Browne]

Jeff, the best solution would be to break this into two Number fields. This
will sort correctly and (more importantly) efficiently, and it is dead easy
to combine the two back into one for reporting purposes later.

If you cannot do that, and the first number is always 2 digits, try Order
By:
Left([ProposalNumber],2), Val(Mid([ProposalNumber],4))