parsing a string

[Guest]

I have a table that contains a field for employee numbers which I use for the
primary key in related tables. This was previously used as a flat file and a
6th alpha character was added to "some" of the numbers as an indicator. This
alpha could be a "Q", "T", "D", "P", or "N". I would like to parse this
character to a different field so I can keep the meaning of the indicator.
Also I need to match the employee ID to the related tables, they will match.
How would I best do this since there is no dilmeter?

 

[Smartin]

I have a table that contains a field for employee numbers which I use for the
primary key in related tables. This was previously used as a flat file and a
6th alpha character was added to "some" of the numbers as an indicator. This
alpha could be a "Q", "T", "D", "P", or "N". I would like to parse this
character to a different field so I can keep the meaning of the indicator.

If you are importing from the flat file you could insert a column break
to separate the elements.

If you already have the data in a table in Access, create two new
columns in the table, EmpID and EmpInd. Make sure "EmpID" has a data
type that is compatible with your related tables. Update these from the
original field as =LEFT(EMPINFO,5) and =MID(EMPINFO,6,1). Do this in a
sandbox instance (copy) first.

Also I need to match the employee ID to the related tables, they will match.
How would I best do this since there is no dilmeter?

As long as you choose a type for EmpID that is compatible with the other
tables, there should be no worries.

 

[Guest]

Thanks for the quick response! The table has been imported from an Excel
file. By the following
"Do this in a sandbox instance (copy) first."

do you mean to copy the table first so I won't mess up the original table?

 

[Smartin]

Thanks for the quick response! The table has been imported from an Excel
file. By the following
"Do this in a sandbox instance (copy) first."

do you mean to copy the table first so I won't mess up the original table?

Better still, copy the entire MDB to a different folder. Oh, and make
sure you don't have any linked tables that might be affected. Better
safe than sorry!