Wrong number of arguments in Duane's Concatenate Module 2

[NeilT123]

I am trying to use Duane's Concatenate module to create a report label but I keep getting a "wrong number of arguements error message. My code is

SELECT tblSltFarm.FarmAccountNumber, Concatenate("SELECT AccountName FROM QrySLTFarm2
    WHERE FarmAccountNumber =" & [FarmAccountNumber], ", ", ", and " ) AS AccountNameLbl
FROM tblSltFarm

Can anyone point out where I am wrong please.

Thank you

 

[dhookom]

Duane here.
Is FarmAccountNumber text or numeric? If text, you would need to add in some quotes.

SELECT tblSltFarm.FarmAccountNumber, Concatenate("SELECT AccountName FROM QrySLTFarm2
    WHERE FarmAccountNumber =""" & [FarmAccountNumber] & """", ", ", ", and " ) AS AccountNameLbl
FROM tblSltFarm

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

Hi Duane, FarmAccountNumber is an autonumber.

I imported the tables from the FAQ example

This works

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID]) as FirstNames
FROM tblFamily

This code gives the wrong number of arguments error.

SELECT FamID,
Concatenate("SELECT FirstName FROM tblFamMem
WHERE FamID =" & [FamID], ",",", and ") as FirstNames
FROM tblFamily

Neil

 

[dhookom]

Open the debug window (press Ctrl+G) and try enter an expression replacing 1234 with a legitimate FarmAccountNumber:

? Concatenate("SELECT AccountName FROM QrySLTFarm2
    WHERE FarmAccountNumber =1234" , ", " )

Make sure you have the latest function from faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

I have the latest function

When I run the debug code I get compile error Expected:end of statement

 

[PHV]

Use a single line in the debug window:
? Concatenate("SELECT AccountName FROM QrySLTFarm2 WHERE FarmAccountNumber=1234",", ",", and ")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Thank you for the replies which have helped me identify where I was going wrong. I think I am almost there.

I thought I would give a bit of explanation as to what I am trying to do. Historically when I have run reports based on multiple customers I have used a continuous report in the header which gives a header something like this:

Report No 1 for periods 3 and 4 for
Account 1
Account 2
Account 3
Account 4

which looks a bit rubbish so I hoping to achieve

Report No 1 for periods 3 and 4 for Account 1, Account 2, Account 3 and Account 4

The following code gives me what I want except there are too many ands and an extra ,.

The result is Customer 1, and Customer 2, and Customer 3, and Customer 4.

What I would like is Customer 1, Customer 2, Customer 3 and Customer 4 so no , after customer 3 and only 1 and.

SELECT Concatenate("SELECT AccountName FROM QrySLTFarm2 WHERE AccountGroup =1",", and ") AS AccountNameLbl
FROM tblSltFarm
GROUP BY Concatenate("SELECT AccountName FROM QrySLTFarm2 WHERE AccountGroup =1",", and ");

Any pointers please as to what to do next?

Thank you

 

[PHV]

SELECT Concatenate("SELECT AccountName FROM QrySLTFarm2 WHERE AccountGroup =1",", "," and ") AS AccountNameLbl

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Hi PHV, unfortunately that takes me back to the "wrong number of arguments" error message.

 

[PHV]

So, you don't have the right version of the function, dot.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

OK, so I deleted the old module, went to Duane's link and copied and pasted the text into a new module and compiled it, with no errors. Ran my query, which works but the result is still "Customer 1, and Customer 2, and Customer 3, and Customer 4" which has too many ands and an extra comma.

I must admit I don't know the difference between DAO and ADO and wouldn't know how to check so pasted Duane's module without changing anything but it did compile.

Running Access 2007 on Windows 7 64 bit. Any suggestions please?

 

[PHV]

which has too many ands and an extra comma
Did you use this ?
SELECT Concatenate("SELECT AccountName FROM QrySLTFarm2 WHERE AccountGroup =1",", "," and ") AS AccountNameLbl

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Thank you PHV, almost there.

That code works except that it cuts the last letter off the customers name before the "and", i.e if there are 3 customers it cuts the last letter off the 2nd name, if there are 4 customers then it cuts the 3rd name and so on.

Have you seen this before? Any suggestions what to do next?

 

[PHV]

I'd replace this:
intLenB4Last - Len(pstrDelim) - 1)
with this:
intLenB4Last - Len(pstrDelim))

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NeilT123]

Thank you PHV. I have given a star to dhookom for his original code and I have tried to give you 2 stars for taking the time to help me through this but I think I can only give one. Thank you

 

[NeilT123]

Unfortunately I am now getting a run time error 5 message Invalid procedure call or argument and debugging highlights these 3 lines

strConcat = Left(strConcat, _
intLenB4Last - Len(pstrDelim)) _
& pstrLastDelim & Mid(strConcat, intLenB4Last + 1)

Any suggestions please as to what to do next?

 

[dhookom]

Both PH and I have suggested testing in the debug/immediate window. Have you tried this? You can set a break point that allows you to mouse over to see the values of all variables. If you don't learn how to do this, you will need to come back here with more and more questions.

If you have tried the debug window with a break point, please provide us with the values of the variables.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

OK, thanks Duane, just trying to get ready for the week so will have a look tomorrow night. The strange thing is that it was all working, or so it appeared yesterday.

 

[dhookom]

Might be only a single value returned or maybe a Null.

Duane
Hook'D on Access
MS Access MVP

 

[NeilT123]

You were correct, it was a single value.