Sort on part of a string

  • Thread starter Thread starter ORJAN
  • Start date Start date
O

ORJAN

In an alphanumeric field (Invnr) I have data like "J1", J2" "J100†etc.
Since the result when sorting on this field is somewhat “peculiarâ€
(J1, J100, J2 etc) I would like to separate the numbers from the “Jâ€
and sort on the number (can be up to 10000). I have tried the following
code in a query without any luck; always wrong syntax.
sort: left([Invnr], InStr([Invnr],"J")+1)
I do not want to add another field on my database.
Can anybody help me, please
 
In an alphanumeric field (Invnr) I have data like "J1", J2" "J100¡ etc.
Since the result when sorting on this field is somewhat ´peculiar¡
(J1, J100, J2 etc) I would like to separate the numbers from the ´J¡
and sort on the number (can be up to 10000). I have tried the following
code in a query without any luck; always wrong syntax.
sort: left([Invnr], InStr([Invnr],"J")+1)
I do not want to add another field on my database.
Can anybody help me, please
Click to expand...

Your Left() function, as written above, will return "J1" or "J2",
etc., as strings, which is not what you want.
You want 1 or 2, or 100, etc., as a number.

Where are you doing this?
In a form?
Create a query that contains all of the fields.
Add a new column to the query.
If the numbers always begin at the 2nd character.....
SortThis:Val(Mid([Invnr],2))

If this is in a report, in Report Design View click on
View + Sorting and Grouping.
As the first Field/Expression, write:
= Val(Mid([Invnr],2))
Ascending
 
The Val(Mid([Invnr], 2)) that the others have posted is probably the best
solution if you know that there will always be only one character at the
beginning of the string.

If you specifically need to scan for the "J" (i.e., if you can have a number
like "AJ1"), then you were on the right track, and you would use:

Val(Mid([Invnr], Instr([Invnr], "J") + 1))


Rob
 
In an alphanumeric field (Invnr) I have data like "J1", J2" "J100” etc.
Since the result when sorting on this field is somewhat “peculiar”
(J1, J100, J2 etc) I would like to separate the numbers from the “J”
and sort on the number (can be up to 10000). I have tried the following
code in a query without any luck; always wrong syntax.
sort: left([Invnr], InStr([Invnr],"J")+1)
I do not want to add another field on my database.
Can anybody help me, please
Click to expand...

You're paying the penalty for violating the principle that fields should be
atomic - having only one value!

A substring operation will return a string - and the text string "14123" sorts
before the text string "2", just as the text string "abacus" sorts before the
text string "b". What you can do - if the field ALWAYS consists of a letter
followed by numbers - is use the Val() function to convert it to a number,
which will sort numerically:

Sortkey: Val(Mid([Invnr], 2))

The Mid() function will extract the substring starting with the second
character ("2", "100"); the Val function will turn that string into a number.

BTW don't use the reserved word Sort.
 
Back
Top