This is a tricky one. It could be done (not easily) with
regular expressions, but I'd like to do it within Jet SQL.
The data is already in a VERY long table, and I'd rather
not call it into a recordset and loop through it.
The field contains zero to seven subfields, with one-char
delimiters. Fields 1, 3, 4, 5, 6 are either two characters
or missing. Delimiters are missing after the last present
field. Field 2 is missing or 1-48 chars. Field seven is
zero to eighty chars.
Delimiters can be different in different rows. If the
field has three or more characters, the third is the
delimiter for the field.
What I need to do is turn this single column into four:
First: the first subfield, if any, else null.
Second: the second, if any, else null.
Third, subfields 3-6 with the delimiter standardized
to a space. Null if all missing or only delimiters..
Fourth, subfield seven if any else null.
If the table were shorter, I could export to CSV,
load into excel, add a column eight placeholder
and dump back into Access, .....
--
Wes Groleau
regular expressions, but I'd like to do it within Jet SQL.
The data is already in a VERY long table, and I'd rather
not call it into a recordset and loop through it.
The field contains zero to seven subfields, with one-char
delimiters. Fields 1, 3, 4, 5, 6 are either two characters
or missing. Delimiters are missing after the last present
field. Field 2 is missing or 1-48 chars. Field seven is
zero to eighty chars.
Delimiters can be different in different rows. If the
field has three or more characters, the third is the
delimiter for the field.
What I need to do is turn this single column into four:
First: the first subfield, if any, else null.
Second: the second, if any, else null.
Third, subfields 3-6 with the delimiter standardized
to a space. Null if all missing or only delimiters..
Fourth, subfield seven if any else null.
If the table were shorter, I could export to CSV,
load into excel, add a column eight placeholder
and dump back into Access, .....
--
Wes Groleau