Sorting by Letters and then numbers with dashes

[Analogkid]

I tried to search on this but could not come up with the proper word for the search critera. So, I have a Query that if a "Letter" is picked, it will query that letter's list of numbers. I want to be able to sort in a report by numeric order. Example below is how it sorts and how I want it to sort. Let's say the Letter is H.

Sorts
H-133-8
H-135-6


H-95-3
H-96-4....

I want it to sort by lowest number to highest. Look like this

H-95-3
H-95-4
H-133-8
H-135-6... etc.

Hope this makes sense!
Thank you for your help

[June7]

This is text so follows alpha sort. Need placeholder 0's, like:

H-095-3
H-096-4
H-133-8
H-135-6

Try creating a field in query with expression and sorting on that constructed field:

Format([fieldname],"A-###-#")

[Analogkid]

Thanks June. But I need to keep the exact format as I posted. Is there any other way without the placeholder of 0's?

I tried the field formula that you quoted but it still starts with H-100-1 (not H-11-2) which is the first one it should be.
Thanks!

[June7]

My suggestion was not to use the Format property of field but to create a field in query. However, that expression really won't work after all. I was trying to use a technique that works with numeric data, doesn't work with text. I am now faced with a similar issue and just tested the approach. Rats!

Will all your values start with a single letter and hyphen and have a second hyphen for the second number sequence? Will have to extract the parts into separate fields and use those constructed fields for sorting.

For the letter part:
Left([fieldname],1)

For the first number part:
Val(Mid([fieldname], InStr([fieldname],"-")+1))

For the second number part:
Val(Mid([fieldname], InStrRev([fieldname],"-")+1))

Every record must have data in that field or these functions will error if Null is encountered. If Null is possible, handle it with Nz function:

Val(Mid(Nz([fieldname],""), InStr([Nz([fieldname],""),"-") + 1))

As you can see, easier to concatenate data than to parse.

The original data in table will not be changed and will still be available for display.

[Analogkid]

Wondering if it would be better to have 3 separate fields? then the 2 number fields could be number fields. Easier to sort? Will take a little while but I could do it in Excel and then add it back into the Access DB.

[June7]

Three fields would be the conventional approach. Then they can be concatenated for display purposes in a report. What you have can be made to work as described if you want to.

[Analogkid]

Now, I need to brush up on my Excel skills with the Find and Replace.
Thanks a lot for your help again.

[Beetle]

If the data is always in the format of the posted example, I'm not sure you would even need to handle the first and second number portions of the string separately. You would need one expression to sort by the letter, which has already been mentioned, but as for the rest of it, you could probably just treat it as one number and sort additionally by that;

Val(Replace(Mid([YourField],3),"-",""))

[June7]

Analog, Beetle's expression for the number parts will work if the second number sequence is always 1 digit.