Parsing problem

[roelandkim]

Hopefully someone can help me out. At my office we send out people to do inspections on a regular basis. An inspector will visit around 20 locations a day. I want to be able to take a list of address' and parse them out into seperate fields. For example:

10 Main Street
24-32 5th Avenue
534 Broadway

Ideally I would like to get the address' to be parsed as such:

PHN________Street Name_____Street Type
10__________Main____________Street
24-32_______5th_____________Avenue
534_________Broadway

I've been able to parse out the PHN and street type, but also need to take into account streets with no street type, such as 534 Broadway.
If any one has any idea on how to accomplish this it would be much appreciated.

Thanks,
Roeland Kim

 

[dcx693]

Use the Instr function to find occurrences of the space character " ", like this:
Instr(1,"10 Main Street"," ").
That will return 3, meaning that the first occurrence of the space character occurs at position 3. The first parameter in the function tells the function which position to start searching at. The second, is the string to search within. The third is the string being sought.

You can do another Instr search using the result of the first find which returned 3. Just add 1 to it, to tell Access to start the search another character to the right, like this:
Instr(Instr(1,"10 Main Street"," ")+1,"10 Main Street"," ").

Then from there, you can pull out the pieces you need using the Mid function like this:
Mid("10 Main Street",1,3-1)
The last parameter (3-1) uses the result of the first Instr function and subtracts the 1 because 3 is where the space character is.

I personally wrote some custom functions to handle string extractions, but I don't know where I put them. You might want to do the same.