Sorting Addresses

[pepegot1]

How can I sort string addresses so as to get the correct number order? I know that strings sort on each character, which causes the problem. I have tried some things (with VAL and Len), but was not completely successful. I do not want to enter an address number as 0630, when I want 630.

Why does this not work:IIf(Len(Str(Val([address]))=3),"0" & [address],[address])? Address has 3 and 4 numbers only.

 

[The_Doc_Man]

Your problem is treating the addresses as monolithic strings.

You might have to do some text parsing to break up the addresses into number, apartment or suite, street, city, etc. This topic has been addressed many times in the forum, rarely to anyone's satistfaction - because the addresses follow no particular pattern.

You won't like this, but your problem occurred a long time before you got to this question. Your data layout does not correspond to the actions you wanted to perform. Therefore, you are now having headaches. If you had captured your data sets with the individual fields separated, you could have written a single query to do all of your sorting at once. Now, you are going to have a text parsing nightmare.

Don't groan when I tell you this, but the best solution to your problem, if it is in ANY way possible, is to redesign the data to support your operation. It is FAR easier to put addresses together than it is to take them apart. Start with them apart and you are much better off.

 

[Dennisk]

Another way is to use the val function to extract the number then pad it out with leading zeros and use that as to sort

Public Function GenerateNumber(intSlotNumber As Integer) As String
GenerateNumber = Right("000" & Trim(intSlotNumber), 3)
End Function