Append query, split one field into two... 1

[wvmbark]

I import a text file (that can't be changed) and then append that data to a history table. The text file is deliminated by "|" (pipes). One field has two text areas always separated by 5 spaces. I want the text before the 5 spaces to append to one field, the text after the 5 spaces to append to another field. What is the expression I'd use to accomplish this?

Thanks!!

 

[RickSpr]

I'm assuming you've already imported the file into a table, based on pipe-delimiters, and you just need to split the one field in an Append query.

The first text area would be
Left$([Fieldname], Instr([Fieldname], " ") - 1)
The second would be
Mid$([Fieldname], Instr([Fieldname], " ") + 5)

Note: This assumes that the field contains at least one group of 5 spaces in every row. The field can not be Null.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wvmbark]

Yes, I've imported and created a spec already. The field I want to split contains both "Discrepancy" and "Corrective
Action" text, separated by 5 spaces.

The field will never be null, though occasionally the second part doesn't exist, see #2 below.

1. Will your expressions provide all text before and all text after the spaces?
2. What if there is no spaces or second part (in the case that there was no "Corrective Action" written yet)?

Thanks so much!!

 

[RickSpr]

With the spaces and second part optional, a change is needed.

Discrepancy: Left$([Fieldname], Instr([Fieldname] & " ", " ") - 1)
Corrective Action: Mid$([Fieldname], Instr([Fieldname] & " ", " ") + 5)

Instr() is the function that searches Fieldname for the spaces. What this change does is append 5 spaces to Fieldname for the search only so that the delimiter is guaranteed to be found.

Yes, to your first question. These expressions will work properly when either or both items are missing, and will return all text before/after the delimiter.

Just one more qualification: If Discrepancy has trailing spaces or Corrective Action has leading spaces, the 5 spaces of separation will be part of a 6- or more space run. These expressions will always interpret the extra spaces as leading spaces on Corrective Action.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[wvmbark]

Thanks Rick, you've been very helpful! I will have to spend some time experimenting with these expressions, so that I totally understand them. I will post back if I have any problems.