separate text into different fields

[Samantha]

I imported a text document (PA_Match_Test) into Access that contains one
field that has the street name, thoroughfare, and post directional. I need
for this to be separated into the Street Name field, Thoroughfare field and
the Post Directional field. I created a table called Post_Directional that
has all the valid values for the post directional and a table called
Street_Suffix that has all valid thoroughfares. I put a space before all of
the Thoroughfares & Post Directionals so that it would only find/move valid
thoroughfares & post directionals; i.e. Broadway would NOT be separated, but
Broad Way would be. Also; another criteria is that only 1 thoroughfare can
go into the thoroughfare field; i.e. Broad St Ext would only put the EXT in
the thoroughfare field. I would like this done either as a macro or VB
scripting. The PA file is the first of many files that I need for this
scripting to work for. Any help would be greatly appreciated!

Thank you,
Sam

 

[Steve Schapel]

Sam,

I am not fmailiar with the terminology you are using here. Could you
post back with a few examples of the existing data, and how you want it
to be separated?

 

[Samantha]

Thank you for the reply...

The existing data that I have is:

Street_Name Street_Suffix Post_Directional
Broadway St Ext
Old Mill Rd S
Broad Way
US Route 1
S Deerfield Rd
Main N
River Avenue North

The way that I need it to be parsed is:

Street_Name Street_Suffix Post_Directional
Broadway St Ext
Old Mill Rd S
Broad Way
US Route 1
S Deerfield Rd
Main N
River Avenue North

I currently have a table set up with all valid Post Directional values (N S
E W NE NW SE and SW) and a table set up with all valid Street Suffixes (there
are 232 of them). When I populated the data in the 2 tables (Post
Directional & Street Suffix), I put a space so that (hopefully) the macro or
VB script can tell that I only need it divided if there is a space between
the street name and the street suffix and the
In the last example that I gave, even though Avenue is a valid thoroughfare,
i need it to stay in the street name field due to the post directional being
spelled out.

Thank you for your help!

 

[Steve Schapel]

Samantha,

It seems to me that you could do this via running sequentially two
Update Queries.

The SQL view of the first one would be something along these lines...

UPDATE YourTable
SET Street_Name = Left([Street_Name],InStrRev([Street_Name]," ")-1),
Post_Directional = Mid([Street_Name],InStrRev([Street_Name]," ")+1)
WHERE Mid([Street_Name],InStrRev([Street_Name]," ")+1)
IN(SELECT [Post_Directional] FROM [PostDirectionalsTable])

Similarly, the SQL of the second query would be something like this...

UPDATE YourTable
SET Street_Name = Left([Street_Name],InStrRev([Street_Name]," ")-1),
Street_Suffix = Mid([Street_Name],InStrRev([Street_Name]," ")+1)
WHERE Mid([Street_Name],InStrRev([Street_Name]," ")+1)
IN(SELECT [Street_Suffix] FROM [StreetSuffixesTable])

You could use a macro to make this happen, via two OpenQuery actions.
Or you could do it in a VBA procedure, using the CurrentDb.Execute
method. Or, if this is just a one-off requirement, just run ther
queries by clicking the toolbar button with the red [!] icon. Either
way, make sure you have a backup of your database!