Parsing Names and Addresses

[John in PA]

Can anyone help me separate names and addresses. I would like to break up First Name, Last Name, Middle Initial, etc. and also break up the address into House Number, Street, etc. The information is in two fields now. "Name" and "Address". Any help greatly appreciated!

 

[dk]

Hi,

Assuming that your data is always in the format First Name, Initial, Last Name then you can use a combination of Left$, Instr, Mid$ and Right$ to get what you need e.g.


SELECT Left$([MyTable]![Name],InStr(1,[MyTable]![Name]," ")-1) AS FirstName, Mid$([MyTable]![Name],InStr(1,[MyTable]![Name]," ")+1,1) AS Initial, Right$([MyTable]![Name],Len([MyTable]![Name]) InStr(1,[MyTable]![Name]," ")-2) AS Surname FROM MyTable;

You will need to adjust the table and field names to suit your table. You can use a similar method to break down the address. Also, you'll run into problems if the data is not in the format described above e.g. no initial in the name.

For what it's worth, this is a messy way to get what you need. If you can, take a look at re-designing your database with an individual field for first name, initial and surname and similar for the address. You'd eliminate the current problem you have and your database would fit more into recognised database design practices.


_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2003-01-12 18:31

 

[John in PA]

Thanks Dan! The data I'm working on was downloaded and I didn't have any control over the fields that's why I'm trying to separate them now. I'm going to try your code today. Thank you so much!