SELECT JSA.ID, JSA.JSANo, JSA.JSATitle, JSA.OriginalDate, JSA.RevisionDate
FROM JSA
WHERE (((JSA.JSANo) Like "SA*."))
ORDER BY JSA.JSANo, Left([JSA].JSANo,1), Val(Mid([JSA].JSANo,2));

This returns:

SA1
SA10
SA11
SA2
SA3

I need it to sort properly. If I remove the *. and just enclose "SA" I get nothing. Could someone help? Thanks.

Change your Order By to this

ORDER BY Val(Mid([JSA].JSANo,2));

Brian,

See if this info helps:

Sort text fields with numeric data in them

Good luck,

Aqua

SELECT JSA.ID, JSA.JSANo, JSA.JSATitle, JSA.OriginalDate, JSA.RevisionDate
FROM JSA
WHERE (((JSA.JSANo) Like "SA*."))
ORDER BY Val(Mid([JSA].JSANo,2));


Is this what you wanted me to change it to ? If so, nothing comes up. The query returns 0 records. Thanks for suggestion.

Hi BrianS,

Actually your code worked.....to a point. I am now getting:

S13
S12
S11
S8
S7
S5

From looking at the results the query is defintely being sorted on the field ID in the JSA table. Here is exactly what I have.

SELECT JSA.ID, JSA.JSANo, JSA.JSATitle, JSA.OriginalDate, JSA.RevisionDate
FROM JSA
WHERE (((JSA.JSANo) Like "SA*"))
ORDER BY Val(Mid([JSA].JSANo,2));

Oh sorry, try this

ORDER BY Val(Mid([JSA].JSANo,3));

Since the number starts at the 3rd position of JSANo

Perfect. Thank You!