Un-Concatenating

M

Micah Chaney

How do you separate the contents of one field into several fields? Sort of
like un-concatenating. I have one field that contains data from several
fields, there's a common delimiter, but how do I code that into a query or
form field?
 
M

Mike Labosh

How do you separate the contents of one field into several fields? Sort
of
like un-concatenating. I have one field that contains data from several
fields, there's a common delimiter, but how do I code that into a query or
form field?
Click to expand...

Queries in Access can call VBA functions, so if I have a column with stuff
like this:

value1;value2;value3

I can use the Instr() function to locate the ; delimiter:

Instr([myColumn], ";")

....and use that position value to call Left$() to get value1:

Left$([myColumn], Instr([myColumn], ";") - 1)

....get the position of the next ; with Instr() and pass them to Mid$() to
get value2:

Mid$([myColumn], Instr([myColumn], ";") + 1, Instr(Instr([myColumn], ";") +
1[myColumn], ";") - Instr([myColumn], ";")) ''This is air code. You'll
need to monkey with it a bit

and then pass the position of the 2nd delimiter to Right$():

Right$([myColumn], Len([MyColumn]) - Instr(Instr([myColumn], ";") +
1[myColumn], ";") )

It's not pretty, but it should work.

Another thing that you could do to get there by cheating is to use the
export wizard to export the table using the same delimiter format as what's
in your table data, then re-import the file to a new table. The extra
values will then come back in as separate columns, and you will likely have
two empty columns, one just before and one just after the new ones from your
old composite values.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
 
M

Micah Chaney

OK -- I'm relatively ignorant when it comes to programming...so let me give
you what I have/need. I have a field [Department] that contains this data
"0 -1 -1 0 0 0 0 -1"
I need that first character (0) to appear as "0" under a [Research] field,
the second character (-1) to appear as "-1" under a [Pull Ticket] field, the
third character (-1) to appear as "-1" under a [Sales Return] field...etc.
How would I set that up? Thanks.

Mike Labosh said:
How do you separate the contents of one field into several fields? Sort of
like un-concatenating. I have one field that contains data from several
fields, there's a common delimiter, but how do I code that into a query or
form field?
Click to expand...

Queries in Access can call VBA functions, so if I have a column with stuff
like this:

value1;value2;value3

I can use the Instr() function to locate the ; delimiter:

Instr([myColumn], ";")

....and use that position value to call Left$() to get value1:

Left$([myColumn], Instr([myColumn], ";") - 1)

....get the position of the next ; with Instr() and pass them to Mid$() to
get value2:

Mid$([myColumn], Instr([myColumn], ";") + 1, Instr(Instr([myColumn], ";") +
1[myColumn], ";") - Instr([myColumn], ";")) ''This is air code. You'll
need to monkey with it a bit

and then pass the position of the 2nd delimiter to Right$():

Right$([myColumn], Len([MyColumn]) - Instr(Instr([myColumn], ";") +
1[myColumn], ";") )

It's not pretty, but it should work.

Another thing that you could do to get there by cheating is to use the
export wizard to export the table using the same delimiter format as what's
in your table data, then re-import the file to a new table. The extra
values will then come back in as separate columns, and you will likely have
two empty columns, one just before and one just after the new ones from your
old composite values.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
Click to expand...
 
M

Micah Chaney

Oh the mighty power of IIf statements. So this is how I pulled this off...I
have a field that's populated in Outlook that concatenates several fields.
Instead of concatenating the fields with a space delimiter I concatenated
them as follows:
"0a 0b 0c 0d 0e 0f 0g". Each "0" value can be either "0" or "-1". These
concatenated fields are all Check boxes, that yield a value of 0 or -1. Then
in my Access Query I coded the following:
FieldA: IIf([ConcatField] Like "*-1a*",-1,0)
FieldB: IIf([ConcatField] Like "*-1b*",-1,0)...
FieldG: IIf([ConcatField] Like "*-1g*",-1,0)
It's a little different, but very effective, because it works. So thanks
for your help Mike, but here's another possibility if this issue shall rise
again. Remember, I suck with VBA, VBS, and VB -- so I guess I got pretty
good and finding ways around it. Anyway, Happy Holidays, and I'm sure you'll
be hearing from me again.

Micah Chaney said:
OK -- I'm relatively ignorant when it comes to programming...so let me give
you what I have/need. I have a field [Department] that contains this data
"0 -1 -1 0 0 0 0 -1"
I need that first character (0) to appear as "0" under a [Research] field,
the second character (-1) to appear as "-1" under a [Pull Ticket] field, the
third character (-1) to appear as "-1" under a [Sales Return] field...etc.
How would I set that up? Thanks.

Mike Labosh said:
How do you separate the contents of one field into several fields? Sort of
like un-concatenating. I have one field that contains data from several
fields, there's a common delimiter, but how do I code that into a query or
form field?
Click to expand...

Queries in Access can call VBA functions, so if I have a column with stuff
like this:

value1;value2;value3

I can use the Instr() function to locate the ; delimiter:

Instr([myColumn], ";")

....and use that position value to call Left$() to get value1:

Left$([myColumn], Instr([myColumn], ";") - 1)

....get the position of the next ; with Instr() and pass them to Mid$() to
get value2:

Mid$([myColumn], Instr([myColumn], ";") + 1, Instr(Instr([myColumn], ";") +
1[myColumn], ";") - Instr([myColumn], ";")) ''This is air code. You'll
need to monkey with it a bit

and then pass the position of the 2nd delimiter to Right$():

Right$([myColumn], Len([MyColumn]) - Instr(Instr([myColumn], ";") +
1[myColumn], ";") )

It's not pretty, but it should work.

Another thing that you could do to get there by cheating is to use the
export wizard to export the table using the same delimiter format as what's
in your table data, then re-import the file to a new table. The extra
values will then come back in as separate columns, and you will likely have
two empty columns, one just before and one just after the new ones from your
old composite values.
--
Peace & happy computing,

Mike Labosh, MCSD

"Mr. McKittrick, after very careful consideration, I have
come to the conclusion that this new system SUCKS!"
~~ General Barringer ~~
Click to expand...
Click to expand...
 
Top