1. ## Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

I inherited the following table:

Name Joe Blow
City Mystic
State CT
Zip 12345

234 Main
Main Street

Customer wants a report sorted by street name and street number.

I need help with query expression syntax that parses out the Street Name and Street Number into separate fields.

If the Address field begins with number, I need to isolate the number into Street Number field and everything after the number to Street Name field.

If the Address field begins with A-Z, there is no Street Number, every thing goes to Street Name field.

Thanks, John Graves

2. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

I can't guarantee complete success with this function. It works on all the examples you provided. One problem is if there is a leading space at the start of the string. That will leave the numbers in with the name but other than that I didn't run into any problems. Also, you have to put the information in a new(different table).
Paul
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strN As String
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("Table4", dbOpenDynaset)
Set rst2 = db.OpenRecordset("Table5", dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
i = 1

rst2.Update
Else
Do Until Not IsNumeric(Mid(rst!Address, i, 1))
strN = strN & Mid(rst!Address, i, 1)
i = i + 1
Loop

rst2.Update
End If
strN = ""
rst.MoveNext
Loop
End Function

3. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

>>One problem is if there is a leading space at the start of the string. <<

You can easily solve this using the Trim function, as in: Trim(rst!Address)

4. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Hi,
If you want this in a query, you should be able to use something like:
Hope that helps.

5. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

When I wrote the code, the Trim(rst!Address) and LTrim(rst!Address) functions didn't help. ???
Paul

6. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Much to easy. Very nice.
Paul

7. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Thanks - I'm lazy and always follow the path of least resistance! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

8. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

I hear that often. One minor problem. I went back to retest the Trim function in my code to be sure I hadn't missed anything. I reentered leading spaces in the strings that began with numbers. Your code ran into the same problem I had. Anything more than a single numeric digit doesn't parse correctly. For example " 123 Main Street" (without the quotes) returns 123 in the first field and 3 Main Street for the second Field. The Trim function seems to bomb with numerics. Not sure why. I couldn't get it to work at any point. Take a look and see if you see a solution.
Still a nicer alternative than my code.
Paul

9. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Not the nicest looking expression in the world but it ought to work!

10. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Success looks good in any form.
Paul

11. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

>>When I wrote the code, the Trim(rst!Address) and LTrim(rst!Address) functions didn't help. ???<<

If you were concerned about leading blanks, then they would have helped. Other than that, they wouldn't have made a difference.

12. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

I was concerned about leading blanks, but a single Trim(rst!Adress) didn't do anything to help so I added the disclaimer instead of code that didin' work. It seems the expression that would havee done it was
Not an expression I tested(or thought to test) but one I'll file away for future use.
Paul

13. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

>>but a single Trim(rst!Adress) didn't do anything to help <<

Using trim doesn't change the underlying field, it just affects the data as presented by the field. So, you have to use Trim each time you reference that field in the subsequent manipulations; or, move it to a variable and use that in equations, as in: strAddress=Trim(rst!Address)

14. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Thanks, that clears some of the confusion as to why it didn't work. Rory's revised expressions are obviously the simpliest solution to the problem. Thanks again.
Paul

15. ## Re: Sort on non-normalized Address Field? (A2k (9.0.4402) SR-1)

Greetings Rory,

100 5th Avenue becomes 1005 5th Avenue using the formula provided.
So I use