I am working in Access 2007. I have two databases, one a new one.
I have the following function in Module1 in both databases - I assume this is the right place for it, as I want to use it repeatedly in different queries. Also, given that it works just fine in one database, it would seem logical.
[i]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
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
ConcatFields = Null
and the following in a query
[i]FullSuburb: ConcatFields(", ",[suburb],[state],[postcode])[/i]
This works perfectly in one, and gives me a compile error in the other.
What is the problem? How do I fix it?