Avatar of msmerry
msmerry
Flag 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?
SQL

Avatar of undefined
Last Comment
msmerry

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
msmerry

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
harfang

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck