combine multiple field values into one field - null values involved

[Dawnit]

Hello there,

I’d like to combine the text values from multiple fields from one table in one new field, for each record, and null values are involved.

There are really a dozen fields to combine, but to make discussion easier, let’s say I have 6 fields to combine:
Expr1
Expr2
Expr3
Expr4
Expr5
Expr6

Each field represents a different office:
Expr1: (Manager)
Expr2: (Clerk)
Expr3: (Finance)
Expr4: (Personnel)
Expr5: (Planning)
Expr6: (Building)
Expr7: (Combined field values)

And each record has a value (office name) in one or more of the fields (field divsions represented by "/"). For example:
Record1: Manager / Clerk / / Personnel / /
Record2: / Clerk / / / /
Record 3: / / / / / / Planning / Building

Here is how I would like the values to look in Expr7, with a comma between office names only when there is another office or more:
Record1: Manager, Clerk, Personnel
Record2: Clerk
Record3: Planning, Building

Here’s the expression I have in Expr7:
Expr7: [Expr1] & (NZ([Expr2]+",") & (Nz([Expr3]+", ") & (Nz([Expr4]+", ") & (Nz([Expr5]+", ") & ([Expr6])))))

And, you can see the results in the attached image. Too many commas!!!!
http://www.mediafire.com/i/?u6sgb1js700k81h

I’d appreciate any and all suggestions, because I’m at my wits end.

Thanks ever so much!

 

[the_net_2.0]

i believe you're looking for something like:

select iif(

iif(nz([expr1], "") = "", "", [expr1] & ",") & 
iif(nz([Expr2], "") = "", "", [Expr2] & ",") &
iif(nz([Expr3], "") = "", "", [Expr3] & ",") &
iif(nz([Expr4], "") = "", "", [Expr4] & ",") &
iif(nz([Expr5], "") = "", "", [Expr5] & ",") &
iif(nz([Expr6], "") = "", "", [Expr6] & ",") = "", NULL, 

iif(nz([expr1], "") = "", "", [expr1] & ",") &
iif(nz([Expr2], "") = "", "", [Expr2] & ",") &
iif(nz([Expr3], "") = "", "", [Expr3] & ",") &
iif(nz([Expr4], "") = "", "", [Expr4] & ",") &
iif(nz([Expr5], "") = "", "", [Expr5] & ",") &
iif(nz([Expr6], "") = "", "", [Expr6] & ","))

as expr7

of course after that you would have to remove the comma at the end, if applicable:

UPDATE table SET 

[expr7] = iif([expr7] is null, NULL, left([expr7], len([expr7]) - 1))

 

[Dawnit]

Found a solution, more at my level too:

Mid(IIf(Len(Expr1), ", " & Expr1, "") & IIf(Len(Expr2), ", " & Expr2, "") & IIf(Len(Expr3), ", " & Expr3, ""),Len(", ") + 1)


thanks though, appreciate your help!!

 

[Dhamdard]

Hello folks,

I have a similar case. I have a primary key that looks like "Bob-CE-123". Now in a query, I want to see only numbers not the whole string. The number associated with primary key is of multiple digits. Can you help what expression must be used to view the numbers only from primarykey field.

Table Name: Activity_Report
Field Name: FormRegNo

Appreciate your assistance