• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Combine varying quantities of fields into a single fields without additional commas (1 Viewer)

dlambert

Member
Local time
Today, 22:54
Joined
Apr 16, 2020
Messages
36
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

I’m here to help
Staff member
Local time
Today, 14:54
Joined
Oct 29, 2018
Messages
12,624
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

Enthusiastic Amateur
Local time
Today, 21:54
Joined
Sep 21, 2011
Messages
6,869
Create a function to concatenate the fields.
In that you can test if a value exists?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 21:54
Joined
Feb 19, 2013
Messages
12,551
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

Super Moderator
Staff member
Local time
Today, 21:54
Joined
Feb 19, 2013
Messages
12,551
good point about nulls - perhaps

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

dlambert

Member
Local time
Today, 22:54
Joined
Apr 16, 2020
Messages
36
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

Super Moderator
Staff member
Local time
Today, 21:54
Joined
Feb 19, 2013
Messages
12,551
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

You've got your good things, and you've got mine.
Local time
Today, 17:54
Joined
May 21, 2018
Messages
3,503
If you build a generic funciton you can use it with any number of fields and it will be easier to reuse
Code:
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
Code:
Paint, Nails
Plywood, Nails
Paint, Plywood
Plywood
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:54
Joined
Feb 19, 2002
Messages
29,803
dbGuy suggested the method I would use but didn't expand and no one else picked up on it or perhaps they didn't know it.

Access uses TWO concatenation operators. The & and it also overloads the + to concatenate when one of the operands is a string. However, the two operators treat nulls differently. The & treats them like ZLS (ZeroLengthStrings) and the + respects the "nullness" of null and returns null when one of the operands is null. This allows you to take advantage of both characteristics when you are concatenating a number of items where one or more may be null and you don't want extraneous separators in the string. The most common example is concatenating the parts of a name without ending up with extra spaces. So, you don't need to create a function, which is "heavy" unless you truly need a variable number of operands. Comment the code so you won't forget what is going on and that the use of both & and + is not just sloppy typing.

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

I enclosed the pairs being concatenated with the + in parentheses because it reminds me of what is going on and triggers someone who doesn't know about this particular attribute that something is different. I'm pretty sure that the code will work without them so do what you want.

Here's an example you can post in the immediate window.

print "a" & ("," + null) & ("," + "c") & ("," + "d") & ("," + null)
a,c,d
print "a" & "," + null & "," + "c" & "," + "d" & "," + null
a,c,d
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 17:54
Joined
Feb 19, 2002
Messages
29,803
Sorry CJ, I saw the mid and didn't notice the +.

The OP said that the desc fields were filled left to right. But if that isn't true, then the expression needs to change to concatenating the "," following the desc and delete any ending coma.
 

Users who are viewing this thread

Top Bottom