Problem with Concatenation

[roaml]

Hi,

Can someone take a look at the query I am using for trying to concatenate various fields into one.

<QUERY>
Category Type: [a_issuetype_call_quality] & IIf(Not IsNull([a_issuetype_call_quality]),"") & IIf(Not IsNull([a_issuetype_coverage]),IIf(Not IsNull([a_issuetype_call_quality]),", " & [a_issuetype_coverage] & IIf(Not IsNull([a_issuetype_translations]),IIf(Not IsNull([a_issuetype_coverage]),", " & [a_issuetype_translations] & IIf(Not IsNull([a_issuetype_other]),IIf(Not IsNull([a_issuetype_translations]),", " & [a_issuetype_other]))))))
<QUERY>

It partly works. I would like to get the results in the sample output below.

<OUTPUT>

Item Quality Coverage Trans Other Cat Type
01 1 1 2 2 1, 1, 2, 2
02 2 2 2, 2
03 3 1 3, 1

<OUTPUT>

Appreciate your help. Thanks.

 

[MajP]

I think

Category Type: ([a_issuetype_call_quality] + ",")  & ([a_issuetype_coverage] + ", ") & ([a_issuetype_translations] + "'") & ([a_issuetype_other])

Null propagation:

string + null = null
string + string = stringstring

string & null = string
string & string = stringstring

 

[MajP]

Disregard what I said. Do not know what I was thinking.
Build a function:

Public Function concNoComma(val1 As Variant, val2 As Variant, val3 As Variant, val4 As Variant) As String
 If Not IsNull(val1) Then
   concNoComma = val1 & ","
 End If
 If Not IsNull(val2) Then
   concNoComma = concNoComma & val2 & ","
 End If
  If Not IsNull(val3) Then
   concNoComma = concNoComma & val3 & ","
 End If
  If Not IsNull(val4) Then
   concNoComma = concNoComma & val4 & ","
 End If
 If Right(concNoComma, 1) = "," Then
   concNoComma = Left(concNoComma, Len(concNoComma) - 1)
 End If
End Function

put this in the query:

concatenate: concNoComma([tblTest]![Field1],[tblTest]![Field2],[tblTest]![Field3],[tblTest]![Field4])

 

[r937]

majp, what you were thinking was that you would use the plus sign to consume the commas, and the ampersand to join the columns, but i'll bet you couldn't figure out how to handle the trailing comma

here we rely on the fact that a column alias in microsoft access can actually be used in the SELECT immediately after it's declared

select id
     , a_issuetype_call_quality
     , a_issuetype_coverage
     , a_issuetype_translations
     , a_issuetype_other
     
     , a_issuetype_call_quality + ', '
       & a_issuetype_coverage + ', '
       & a_issuetype_translations + ', '
       & a_issuetype_other + ', '
                            as RawConcat
                            
     , left(RawConcat
          ,len(RawConcat)-2)  as CategoryType
     
  from samples

neat, eh?

r937.com | rudy.ca

 

[roaml]

Thank you so much MajP and Rudy (that's my son's name).

Rudy,

I used your SQL and it seems to work great. The only issue is, if there is no data in any of the fields, it places an "#Error" in the concatenated field. Is there some way that I can get around this or replace blank fields with the word "Unknown"?

Thanks again to the both of you for your responses.

:-D

 

[r937]

that's funny, i tested it on your data from the original post and it worked fine, but i created the table with text fields

are yours numeric fields?

r937.com | rudy.ca

 

[MajP]

That is why I built the function. It handles both string and numeric and can be expanded to as many fields as you want. Also in my field replace tblTest!Field# with your field names. The function works for your data set.

 

[ZmrAbdulla]

r937,
A personal question..
Is there anything special you are using to write SQL? I have noticed your SQL is different than others..
Thanks

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[r937]

zameer, i use a text editor

r937.com | rudy.ca

 

[roaml]

Hi All,

Thanks so much for helping out.

MajP, I placed the function code in a Module and the query code in my query. I receive the following error:

"Undefined Function conNoComma in Expression"

Did I do something wrong?

Rudy, I am using all text.

Thanks again.

 

[MajP]

You named it "conNoComma" instead of "concNoComma" in the query. I used that name since it concatenates then leaves no comma at the end.

 

[MajP]

Actually this function is better. You can pass any amount of fields or string or numeric

Public Function concFields(ParamArray varFields() As Variant) As String
 Dim intCount As Integer
 For intCount = 0 To UBound(varFields)
 If Not IsNull(varFields(intCount)) Then
   concFields = concFields & varFields(intCount) & ","
 End If
 Next intCount
 If Right(concFields, 1) = "," Then
   concFields = Left(concFields, Len(concFields) - 1)
 End If
End Function

 

[roaml]

Actually MajP, I spelled the error word incorrectly on my last post. I actually entered it correctly in my query as "concNoComma".

I used the other code provided, but it still gives me the same type of error. Here is my module and query.

<BEGIN MODULE>

Public Function concFields(ParamArray varFields() As Variant) As String
Dim intCount As Integer
For intCount = 0 To UBound(varFields)
If Not IsNull(varFields(intCount)) Then
concFields = concFields & varFields(intCount) & ","
End If
Next intCount
If Right(concFields, 1) = "," Then
concFields = Left(concFields, Len(concFields) - 1)
End If
End Function

<END MODULE>

<BEGIN QUERY>

NetCategory: concFields([network_table]![ a_issuetype_call_quality],[ network_table]![ a_issuetype_coverage],[ network_table]![ a_issuetype_translations],[ network_table]![ a_issuetype_other])

<END QUERY>

Thanks again.

 

[ZmrAbdulla]

Thanks Rudy

________________________________________________________
Zameer Abdulla
Help to find Missing people
My father was a realistic father; not a vending machine dispense everything I demanded for!!

 

[MajP]

roaml,
It looks right to me. It appears that it can not see the function, and it appears that you spelled everything right. The only thing I could think of was that you may have put the subroutine in a class module (to include form and report modules which are class modules) instead of a standard module. If the class is not instantiated I do not think it can call the function.