[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Parcing a field in Microsoft Access

Posted on 2006-04-11
11
Medium Priority
?
197 Views
Last Modified: 2008-02-01
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
Comment
Question by:PAPA1964
  • 5
  • 3
  • 3
11 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16428478
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
 

Author Comment

by:PAPA1964
ID: 16428544
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16428690
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:PAPA1964
ID: 16428775
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
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 16430155
Well - do you have the tablename and all field names in question.
Then, you never know, the air code may just run ;-)
0
 

Author Comment

by:PAPA1964
ID: 16430375
Yes, I do know the table name and field names. If this works, I need to clean up other fields also.

Thanks
0
 
LVL 44

Expert Comment

by:GRayL
ID: 16430604
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
 

Author Comment

by:PAPA1964
ID: 16430727
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
 
LVL 44

Accepted Solution

by:
GRayL earned 750 total points
ID: 16431581
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
 
LVL 44

Expert Comment

by:GRayL
ID: 16563099
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
 

Author Comment

by:PAPA1964
ID: 16563527
You are right the answer deserves an 'A' .

Thanks,
Srilaxmi
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question