Search/Extract Specific Data from all fields in a table

I have imported data from a text file into a table. In the original text
file, each record sits on its own line as a long string and each
distiguishable field is separated by a unique identifier (i.e. {1000}first
name{1100}last name{1200}city etc.). When importing the data I indicated
the left bracket "{" as the field separator so the data went into the table
like this:

Field2 Field3 Field4
1100}02P N 1110}06031023FT01 1120}20090603QMGFNP3102

I am trying to extract the data from a field by recognizing the beginning
characters ("1100}") and only extracting the data that follows ("02P N") into
a new field in a new table. One of the problems that I am running into is
that each record may not have all of the particular fields in the text
string. One record may have 20 noted fields separated by the unique
identifier and another may only have 16 of those fields. Which means that
record {1100} may be in Field2 on one record but in Field4 in another record
when being imported. Any help would be much appreciated!

I suggest you use an intermediate table to import your data. Then you can
parse the fields in the intermediate table and put them into the correct
fields in the destination table.

Basically, if you have 1100}Smith in the intermediate table in the second
field, then you can look at the first four chararcters to determine the
destination field, then strip off the first 5 characters and store that in
the destination last name field.

Hopefully you are familiar with VBA becuase it will take that to do the task.
--
Dave Hargis, Microsoft Access MVP


"AD" wrote:

I have imported data from a text file into a table. In the original text
file, each record sits on its own line as a long string and each
distiguishable field is separated by a unique identifier (i.e. {1000}first
name{1100}last name{1200}city etc.). When importing the data I indicated
the left bracket "{" as the field separator so the data went into the table
like this:

Field2 Field3 Field4
1100}02P N 1110}06031023FT01 1120}20090603QMGFNP3102

I am trying to extract the data from a field by recognizing the beginning
characters ("1100}") and only extracting the data that follows ("02P N") into
a new field in a new table. One of the problems that I am running into is
that each record may not have all of the particular fields in the text
string. One record may have 20 noted fields separated by the unique
identifier and another may only have 16 of those fields. Which means that
record {1100} may be in Field2 on one record but in Field4 in another record
when being imported. Any help would be much appreciated!