how do I filter data to exclude words in access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am importing data from Excel to Access. I need to separate the name
fields, which have Last, first into two separate fields in Access Last and
First. I need to know how to filter the data to separate it.
 
if you're importing the data using the TransferSpreadsheet method, or
manually importing from File | Get External Data | Import on the menu bar,
then suggest you import the data into a temporary "holding" table. add two
extra fields to the table, for FirstName and LastName. after the import, run
an Update query on the holding table to split the name field and add the
names to the extra fields. try the following expressions in your Update
query, as

to get the last name:

IIf([HoldingTable].[NameField] Is
Null,Null,Left(([HoldingTable].[NameField],InStr(1,([HoldingTable].[NameFiel
d],",")-1))

the above goes all on one line in the query.

to get the first name:

IIf(([HoldingTable].[NameField] Is
Null,Null,Right(([HoldingTable].[NameField],Len(([HoldingTable].[NameField])
-InStr(1,([HoldingTable].[NameField]," ")))

again, the above goes all on one line in the query. substitute the correct
names of the holding table and the name field, of course. after running the
Update query, append the fixed records from the holding table into the
"real" data table.

hth
 
Splitting names into two fields is one of the most difficult things to do in
any programming language. That is because there are so many possibilities,
and you can't always tell which you are dealing with. for Example:

John Van Horne

Could be First Name John, Middle Name Van, Last Name Horn

Or could be First Name John, last Name Van Horn

Charles Alexander Jr

Last Name Jr ?

Sir Thomas Smith

First Name Sir ?

See what I mean. This will take some knowledge of string handling
functions. In VBA, the Split function can be useful for something like this.

You may want to consider a table of Prefixes and Suffixes to validate against.

One technique is to work from both ends of the sting to the middle, looking
for prefixes, suffixes, commas, periods, etc.

Good Luck.

You can get close, but there will always be problems.
 
Back
Top