Combine multiple field values into one - 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:
Expr1Manager)
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 divisions 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!!!!

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

Thanks ever so much!

 

[dhookom]

Try play with something like:

Expr7: [Expr1] & ", " + [Expr2] & ", " + [Expr3] & ", " + [Expr4] & ", " + [Expr5] & ", " + [Expr6]

I hope your actual table isn't stored with table structure.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

As Duane alluded to your database is likely not well designed. I would look at redesign.
If you had to do this you can build a function

Public Function getCombinedFields(seperator As String, ParamArray theFields() As Variant) As String
  Dim varItm As Variant
  For Each varItm In theFields
    If Not Trim(varItm & " ") = "" Then
      If getCombinedFields = "" Then
        getCombinedFields = varItm
      Else
        getCombinedFields = getCombinedFields & seperator & varItm
      End If
    End If
  Next varItm
End Function

Then in a query

select getCombinedFields(", ",[Field1],[Field2],...[FieldN]) as CombinedField....

This allows as many fields as you want and allows you to specify the seperator.

 

[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!!