Concatenate Query

[iwelcomesu]

Hi All,

I am useing below code to concatenate different fileds, except % and date where it exstis in the fileds, after comma separated values from table.

A small bug found in the code, where the total filed [Expr10] of data unable to concatenate in filed as "Name" (query: 1_1)
attached screenshot and sample dbf for your reference.

can you help in this regard.

Public Function fConcatValues(ParamArray aFields() As Variant)

Dim i As Long
Dim strReturn As String

For i = LBound(aFields) To UBound(aFields)
If IsDate(aFields(i)) Then 'Screen out text that looks like a date and date fields
'Do nothing
ElseIf InStr(aFields(i) & "", "%") > 0 Then 'Screen out text fields that have a percent sign, does not screen actual number fields that formatted to show percentages
'Do nothing
ElseIf Len(aFields(i) & "") > 0 Then 'Skips Nulls and zero-length string fields
strReturn = strReturn & ", " & aFields(i)
End If
Next i

fConcatValues = Mid(strReturn, 3) 'Trim leading space and comma and return the concatenated value
End Function

[SoftwareMatt]

It leaves some of the rows blank because it sees the data as a date and you tell it to leave it blank as below:

If IsDate(aFields(i)) Then
'Do nothing

Either your data needs changing or you need to change your code to something like this:

If IsDate(aFields(i)) Then
strReturn = strReturn & ", " & aFields(i)