Extracting Text from Access Fields into New Fields

Extracting Text from Access Fields into New Fields

All too often, people will put multiple data sets into one field in a table. This could hinder querying or reporting on the data or if you were to convert databases down the road.

I encountered data like this in Access and needed to extract it:

A1234567 -> Okay!

A1234567/B1234567

and even:

A1234567/B1234567/C1234567

What is the formula to extract this?

We want to break this into three fields in an Access Query.

To grab the first set regardless of how many entries:

Field1: IIf(Mid([FIELD],9,1)='/',Left([FIELD],8),[FIELD])

To extract the second field:

Field2: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Mid([FIELD],10,8),IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)<>'/',Right([FIELD],8)," "))

Finally, the third field:

Field3: IIf(Mid([FIELD],9,1)='/' And Mid([FIELD],18,1)='/',Right([FIELD],8)," ")

So that was the easy one. Why? This was easy because all the data was the same length. What would you do about cases where you encounter variable data?

CompanyA

CompanyA/CorpB

CorpB/CompanyA

What is the formula to extract this?

Again, we want to break this into two fields in an Access Query.

To grab the first set regardless of how many entries:

Field1: IIf(([FIELD]) Like "*/*",Left([FIELD],InStr(1,[FIELD],"/")-1),[FIELD])

For the second field:

Field2: IIf(([FIELD]) Like "*/*",Right(Trim([FIELD]),Len(Trim([FIELD]))-InStr(1,[FIELD],"/")),"")

If you were converting from Access to SQL Server, for example, I suggest doing the work before converting. Access offers good querying tools and it is a good idea to take advantage of them. Don’t carry forward the mistakes of the past if you don’t have to!