breaking down parts of an electronic feed

[Abby Lee]

I get a comma delimited file that I throw into an Access DB

One filed is a combo of things department, user, group
example:
320 Hurst, T ADM
320 KERNER C PI
320 800 Access TIP
320 POURMAN E MICS

Is there a way to pull the user out of this to create another field that
looks like
Hurst, T
KERNER C
800 Access
POURMAN E

 

[tina]

well, once you import that text file into a table, you could run an Update
query to add the "pulled out" values to another field in the table, using
the following expression, as

Right(Left(str, InStrRev(str, " ") - 1), Len(Left(str, InStrRev(str, " ") -
1)) - InStr(1, str, " "))

everywhere you see "str", replace it with TableName.FieldName, specifically
the name of the table the text file was imported to, and the name of the
field where the "complete" string is stored.

the above expression assumes that the data you want to extract is *always
all the data* between the first and last spaces in the string.

note that it's an ugly expression, and calls a function redundantly, but it
did work in my test. if it runs very slowly, post back and i'll write it
into a function that removes the redundancy - that may help speed it up.

hth