Combine varying quantities of fields into a single fields without additional commas

[dlambert]

Hello all,

I have a table that contains a list of materials, with 7 separate fields for descriptions segments. These are called ADesc, BDesc, CDesc, DDesc, EDesc, FDesc, GDesc.
I would like to display the combined description into a single field output of a query, so at the moment i have the following:

Description: [ADesc] & ", " & [BDesc] & ", " & [CDesc] & ", " & [DDesc] & ", " & [EDesc] & ", " & [FDesc] & ", " & [GDesc]

This works OK but i have the following problem:
Some of the materials do not have descriptions in all fields, so for example there might be content in ADesc, BDesc, CDesc, but nothing in DDesc, EDesc, FDesc, GDesc. So the result is:

ADesc, BDesc, CDesc, , , ,
(note the extra commas)

The quantity of description fields used will vary entry by entry, but will always be from left to right (A to G)

I can imagine a way of doing it using a long series of nested iif statements, but would anyone have an idea of how to get rid of the extra commas hopefully in a more 'elegant' way?

The query gets displayed in a form, maybe i can more easily get rid of the commas through some formatting in the form?

Any advice would be much appreciated.

 

[theDBguy]

Hi. It begs the question why your table structure is like this, but look up the "propagation of nulls" to do what you want.

 

[Gasman]

Create a function to concatenate the fields.
In that you can test if a value exists?

 

[CJ_London]

sounds like your table is not properly constructed, it should be on description per record - perhaps something like

DescriptionPK
FKtoParentTable
Segment (A, B, C, whatever)
SegmentDesc

then would not be a problem.

As it is you will either need a function or repetitive code which might look like this

Description: mid(iif(ADesc<>"","," & ADesc) & iif(BDesc<>"","," & BDesc) &........, 2)

putting the comma first means you only need to remove the first one

note description is a reserved word and should not be used for field names

 

[CJ_London]

good point about nulls - perhaps

Description: mid(("," +ADesc) & (","+BDesc) & ........, 2)

 

[dlambert]

Thankyou for the responses, quick summary of why i have the 7 description fields:

I am storing data on various types of equipment. Some for example i have descriptions as follows:

Relay:
ADesc (Coil voltage): example: 24VDC
BDesc: (Contact configuration): example: 4PDT
CDesc: (Rated Current): example: 16A
Should be displayed as: 24VDC, 4PDT, 16A

Cable:
ADesc (CableType): example: "Marine"
CDesc (NumerOfCores): example: "3 core"
BDesc (Size): example: "1.5mm"
DDesc (Voltage Rating): example: "500VAC"
EDesc (Colour): example: "Grey"
FDesc (FireRating): example "IEC60332-1"
Should be displayed as: Tri-Rated, 3 core, 1.5mm, 500VAC, Grey, IDC60332-1

I have another table that stores the headings for each of the 7 possible fields for each of the materials types (relays, cable, etc).
I use the separate description fields to do easy filtering when browsing for parts and to keep the description consistent from entry to entry.

CJ_London: you mention the use of "Description" as a reserved word. I only have it when i 'rename' the combined descriptions in the query (no table variables are directly called '"Description"). Could this still be a problem at some stage? (i have seen no problems at the moment)

Inspired from the above comments, this is the function i ended up using and it is doing what i need.
Description: Mid((", "+[ADesc]) & (", "+[BDesc]) & (", "+[CDesc]) & (", "+[DDesc]) & (", "+[EDesc]) & (", "+[FDesc]) & (", "+[GDesc]),2)
(now i have to learn what this function is actually doing because i don't really understand how it works)

Thanks all for the guidance.

 

[CJ_London]

Using reserved words can cause errors with misleading messages because the reserved word has a meaning to sql and/or vba. Surrounding them with square brackets often solves the problem, but not always simply better not to use them.

description is a property of a form so if in a form you refer to description meaning the field, vba might interpret that as the form property.

desc is used to define the direction of a sort, etc



99% of the time it is not a problem because the ambiguity does not occur. But when it does you will get misleading error messages.

google ‘access reserved words’ to find out more

 

[MajP]

If you build a generic funciton you can use it with any number of fields and it will be easier to reuse

Public Function ConcatFields(ParamArray FieldValues() As Variant) As String
  Dim i As Integer
  For i = 0 To UBound(FieldValues)
    If Not IsNull(FieldValues(i)) Then
        If ConcatFields = "" Then
          ConcatFields = FieldValues(i)
        Else
          ConcatFields = ConcatFields & ", " & FieldValues(i)
        End If
    End If
  Next i
End Function
Public Sub Testit()
  Debug.Print ConcatFields("Paint", Null, "Nails")
  Debug.Print ConcatFields(Null, "Plywood", "Nails")
  Debug.Print ConcatFields("Paint", "Plywood", Null)
  Debug.Print ConcatFields(Null, "Plywood", Null)
End Sub

results

Paint, Nails
Plywood, Nails
Paint, Plywood
Plywood

 

[CJ_London]

no one else picked up on it

see post #5

Description: [ADesc] & (", " + [BDesc]) & (", " + [CDesc]) ....

- and if ADesc is null and BDesc isn't?