Sorting Numbers in Text Field

[aids2]

I know there have been questions on this subject previously however the fix doesnt seem to work for me.
I have data in a field like AD/DHC-8/1 and AD/DHC-8/100, when I print the report from this data the items are not listed sequentially like I need. I cannot edit the data as its imported from another program on a weekly basis.
How can I sort this data to produce reports that go from 1 to 100 in the correct order?

 

[BoxHead]

If you base your report on a query instead of the table itself, you can use the Mid() and/or Instr() functions to parse out the part of the value you need for sorting.

If the field always begins with AD/DHC-8/ (nine characters), Mid(fieldName,10) would return the numbers at the end.


HTH



When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[aids2]

Thanks for the reply, I do have the report based on a query but there are 25000+ records and they dont all start with the same prefix, however the common trait is that the numbers I need to sort on are all after the second slash mark.

e.g. AD/DHC-8/1 or AD/Gen/51, so the question is how do I get the query to sort on the data ofter the second slash mark all the time, bearing in mind the text before the mark isnt always the same number of characters?

 

[PHV]

Val(Mid([fieldname],1+InStrRev([fieldname],'/')))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya aids2 . . .

[blue] . . .they dont all start with the same prefix . . .[/blue]

In case you desire a dual sort (sorting on the part before the 2nd [purple]/[/purple], followed by the numeric part), in a query setup two [blue]custom fields[/blue] and sort with these. The first:

[blue]Sort1:Left([[purple][b]FieldName][/b][/purple], InStrRev([[purple][b]FieldName][/b][/purple]],'/'))[/blue]

The second:

[blue]Sort2:[b]PHV's[/b] Code[/blue]

See Ya! . . . . . .

 

[aids2]

Thanks for the heads up, I have created 2 fields but keep on getting a syntax error using the statement below
Sort1:Left([TI-NUMBER-TM] , InStrRev([TI-NUMBER-TM]],'/'))

The error is with the first comma.

 

[PHV]

Depending on your locale setting you may have to use a semicolon ([!];[/!]) instead of a comma.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[aids2]

Thanks for all the help, it turns out the , had to be replaced with $.

 

[TheAceMan1]

aids2 . . .

Not quite sure what ya got there but follow this:
[ol][li]Open the report query in design view.[/li]
[li]Remove the 2 fields you added.[/li]
[li]Remove any current sorting.[/li]
[li]On the [blue]first empty field on the field line[/blue] ([purple]on the right[/purple]), copy/paste the following:

[blue]Sort1: Left([TI-NUMBER-TM],InStrRev([TI-NUMBER-TM],'/'))[/blue]

[/li]
[li]In the next field on the right, copy/paste the following:

[blue]Sort2: Val(Mid([TI-NUMBER-TM],1+InStrRev([TI-NUMBER-TM],'/')))[/blue]

[/li]
[li]Set your sorting and you should be set.[/li][/ol]

See Ya! . . . . . .