troubleshooting Question

Concatenation

Avatar of msmerry
msmerryFlag for Australia asked on
SQL
3 Comments2 Solutions323 ViewsLast Modified:
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]
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?
ASKER CERTIFIED SOLUTION
msmerry

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 3 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 3 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros