Link to home
Start Free TrialLog in
Avatar of msmerry
msmerryFlag for Australia

asked on

Concatenation

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
End If
Next
i = Len(strOut) - Len(strDelim)
If i > 0 Then
ConcatFields = Left(strOut, i)
Else
ConcatFields = Null
End If
End Function[/i]

Open in new window

and the following in a query
[i]FullSuburb: ConcatFields(", ",[suburb],[state],[postcode])[/i]

Open in new window


This works perfectly in one, and gives me a compile error in the other.

What is the problem?  How do I fix it?
ASKER CERTIFIED SOLUTION
Avatar of msmerry
msmerry
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of msmerry

ASKER

Why did I accept my own solution?  Because I had solved my problem.  I could have deleted the question as harfang suggests, but I suppose I didn't think of that or know how.  Also, my solution might help others - I can't be the only one who does silly things.  However, I am happy to give all the points to harfang, because it is an appropriate answer, and improves my knowledge.<br />Thanks harfang.