Parsing in a Query

[ALui]

I have a primary key, ULI, that has patient numbers in the format of #####-####, which are blacked out in the picture. The problem is the low number ULI's are substitutes for the real ones and I need a way to get the highest of these low numbers, but they are stored as text (because of the dash inbetween the numbers).

I cut out all the real ULI patient numbers using a query, but when I order the query in ascending, it goes 1, 10, 11, 2, 3, 4, 5, 6 etc. What I need is some way to parse/convert all of this text into actual numbers, which would order properly when sorted ascending (1, 2, 3.... 4, 5, 6, 7, 8, 9, 10, 11, 12 etc)

How can I do this?

 

[Galaxiom]

Do you need just part of the string ordered? Or would it be Ok to just drop the hyphen and convert to a number?

Val(Replace([fieldname],"-","")) removes the hyphen and converts the string to a number

Otherwise get the right or left characters if the format is fixed.

Val(Right([fieldname],4)) to get the right hand four characters and convert to a number

Val(Left([fieldname],5)) similarly with left

Or you can split on the hyphen if the format may vary the number of characters.
Post back if this is the problem.

 

[ALui]

Worked great with the first one, thanks!