Field splitting from a text file

[gguy]

I am importing a text file into an Access table from an OnBase system.

The text file is the result of an inspection. The text file has 19 fields.
One field out of those 19 is an inspection code field and unfortunately, it has varying numbers of inspection codes in that field. Each code is 3 characters long and relates to a certain type of violation. The field will always have at least one code and could have any number codes, there is no limit, but 99% of the time has less than 10 codes.

Another code on the text file is an ID number that corresponds to a dealer ID in the contact table in the database.

I need to break out these codes into a really basic table. The user is asking me to just give him the ID and code. So, if the ID = 700 and the field had 030, 171, 089, 441 then our table would be something like;

ID | Insp code
700 | 030
700 | 171
700 | 089
700 | 441

Any help on this would be greatly appreciated.

 

[pbaldy]

Look at the Split() function. You'd have to use VBA code. Within a loop of the text file records, you'd loop that field with the Split() function and append a record to your table for each one.

 

[murray83]

sorry posted in wrong topic