Test the function in the Immediate window, by enterining:
? ConcatFields ", ", "First bit", "2nd bit")
If that works, the function is working.
You could try putting the calculated field into your query, i.e. type
something like this into a fresh column of the Fields row:
FullAddress: ConcatFields(", ", [address1], [address2], [city])
If that works, you could then use a text box bound to FullAddress field.
Sometimes reports are finicky about referring to fields that are not
represented by a control on the report. You could add the other fields as
text boxes and set their Visible property to No.
If you use a calculated control on a report, make sure its Name is not the
same as the name of one of the fields.
HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Tim Long said:
Hi Allen, I've saved the function into a module and placed an unbound text
box containing the function and arguments into my report. The underlying
query contains the argument fields (Address 1 etc), but when I run the
report
I get an "Enter Parameter Value" message for the ConcatFields field. Any
idea
what I'm doing wrong? Should ConcatField be included as a field in my
query?
Many thanks again
Tim Long
Allen Browne said:
Paste the function below into a standard module, and save.
Then in your form or report, set the Control Source of your text box to:
=ConcatFields(", ", [address1], [address2], [city])
The first argument should be the separator (comma and space in this
example), and then you can list as many fields as you need.
Function ConcatFields(strDelim As String, ParamArray varList()) As
Variant
Dim strOut As String
Dim i As Integer
For i = LBound(varList) To UBound(varList)
If Not IsNull(varList(i)) Then
strOut = strOut & varList(i) & strDelim
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function
Hello, I want to concatenate the strings from several fields (address1,
address2, address3, city etc) into a single field, wioth the strings
separated by a comma, but I want the query to ignore empty fields so
that
I
don't get "Building A, Street B,,Los Angeles" etc (commas enclosing
blanks).
Can you tell me how I can do this? Any help would be much appreciated.
Tim Long
