• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 202
  • Last Modified:

Parcing a field in Microsoft Access

Hello,

I have a address filed which contains data like this - Jacksonville, FL 32205-9351
I like to be able to separate it into city, state and zip fields.
Sometimes this filed contains only the address like - David Lipscomb University (without city, state and zip)
FYI I do not know VB. Is there anything I can do in Access.

Many thanks in advance.
0
PAPA1964
Asked:
PAPA1964
  • 5
  • 3
  • 3
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
If there's no consistency to it at all - then you're pretty knackered.
If you can identify components of the text in a regular, pre-defined manner then it can be parsed out to different fields.

The first address you give as an example has no initial address component - and the latter nothing but that.
If you can imply that no comma means it's just the first line - then so be it - but where would that go?
0
 
PAPA1964Author Commented:
Yes, unfortunately there is no consistency. If the field has just the address like this - David Lipscomb University , I want this to remain in the field it is now and the other one - Jacksonville, FL 32205-9351 should be separated into different fields.

Thanks
0
 
Leigh PurvisDatabase DeveloperCommented:
When not doing this in code - it can get ugly in an SQL statement...
For example just the city might be

UPDATE YourTable Set city = IIF(Instr(Nz(YourField),",")>0,Left(YourField,Instr(YourField,",")-1),Null)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PAPA1964Author Commented:
Thanks for your response. If you have the VB code please do send me, I will have some one help me with that.

Thanks again.
0
 
Leigh PurvisDatabase DeveloperCommented:
Well - do you have the tablename and all field names in question.
Then, you never know, the air code may just run ;-)
0
 
PAPA1964Author Commented:
Yes, I do know the table name and field names. If this works, I need to clean up other fields also.

Thanks
0
 
GRayLCommented:
If you can say that the city is always comma separated from the State abbreviation which is always two letters, and anything thereafter is alway the zip code, then you could try this, presuming the table Adresses has the field Address and three blank text fields, City, State, and Zip.  Always work from a backup:

Insert Into Addresses (City, State, Zip) SELECT
Left(address,Instr(address,","),-1 as City,
Mid(address,instr(address,",")+2,2) as State,
Mid(address,instr(address,",")+5) as Zip
FROM Addresses;
0
 
PAPA1964Author Commented:
Yes, there is a field called Addrress. There are already city, state and zip fields with some data for some records. If I try this code will it delete the existing data from these fields?
And as I mentioned earlier, in the address field there is also data like this - 5000 Longleaf Ln (no city, state or zip)
Is this code going to work then?

Thanks
0
 
GRayLCommented:
Yes, it will overwrite anything that is already there.  Rather than do the Insert initially, tryrunning the Query from SELECT on.  This will show you what it will create. If there is no comma in the string, nulls will be inserted in City, State and Zip.  I had to modify the query to cater to the cases where there is no comma:

INSERT INTO Addresses (City, State, Zip) SELECT
IIF(Instr(address,",")=0,Null,Left(address,Instr(address,","),-1) as City,
IIF(Instr(address,",")=0,Null,Mid(address,instr(address,",")+2,2)) as State,
IIF(Instr(address,",")=0,Null,Mid(address,instr(address,",")+5)) as Zip
FROM Addresses;
0
 
GRayLCommented:
Thanks, but why the B? What did I not provide?  You may not have liked the limitations in using such a query, but that is caused by the variation in your data, not the query.  
0
 
PAPA1964Author Commented:
You are right the answer deserves an 'A' .

Thanks,
Srilaxmi
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now