ConcatReated Error.

[Homegrownandy]

I'm using Allen Browne's ConcatRelated Function. I've been using it for a while now with no issue. Today I'm getting the following error and I have no idea why.



runtime error 3061 Too few parameters. Expected 1:

Code:

Description: ConcatRelated("Description","concatrelated_DWI","Job_ID = " & [job_ID])

I'm using this also and this works with no issue:

Description: ConcatRelated("Description","concatrelated_test","Job_ID = " & [job_ID])

My guess is.. for what im doing right now there is nothing to concatenate. But I'm, pretty sure ive been in this situation before and its still worked using that line.


~Here is the full SQL:

SELECT Concatrelated_DWI.Site_Name, ConcatRelated("Description","concatrelated_DWI","Job_ID = " & [job_ID]) AS Description, Concatrelated_DWI.Date_Complete, Sum(Concatrelated_DWI.Cost) AS SumOfCost, Concatrelated_DWI.Job_ID
FROM Concatrelated_DWI
GROUP BY Concatrelated_DWI.Site_Name, ConcatRelated("Description","concatrelated_DWI","Job_ID = " & [job_ID]), Concatrelated_DWI.Date_Complete, Concatrelated_DWI.Job_ID;

Any advice on where I should be investigating would be appreciated.

[ranman256]

You didn't provide the function code, but
If the field [job_id] is null ,it could fail.

[ssanfu]

You could try:

Description: ConcatRelated("Description","concatrelated_DWI","Job_ID = " & NZ([job_ID],0))

so a NULL will be converted to a zero.

or

I would think [job_ID] should NEVER be NULL (is it a PK/FK field?), but you might try:

SELECT Concatrelated_DWI.Site_Name, ConcatRelated("Description","concatrelated_DWI","Job_ID = " & [job_ID]) AS Description, Concatrelated_DWI.Date_Complete, Sum(Concatrelated_DWI.Cost) AS SumOfCost, Concatrelated_DWI.Job_ID
FROM Concatrelated_DWI
WHERE Concatrelated_DWI.Job_ID IS NOT NULL
GROUP BY Concatrelated_DWI.Site_Name, ConcatRelated("Description","concatrelated_DWI","Job_ID = " & [job_ID]), Concatrelated_DWI.Date_Complete, Concatrelated_DWI.Job_ID;

Hard to test without examples




BTW, "Description" is a reserved word in Access and shouldn't be used as an object name.
I use "Desc"... but "Desc" (and "Description") are not very descriptive.... "Description" of what?
I try to use a prefix: "ItemDesc", "JobDesc", "Job_Desc", .........

[CJ_London]

what is concatrelated_DWI?

If it is a query with parameters, it can generate the error you are getting

Also Description and Date are reserved words, so may be causing the issue

And you are calling your concatrelated value the same as an existing field name

[Homegrownandy]

Hi all,

I appreciate your input regarding the use of reserved words. I was aware of this to be honest more recently, but since its been working for so long I haven't changed it. I'll Change the fields related to this problem and the associated querys today. Even if it doesn't fix it, its a good excuse to get it right.

Thanks, I'll post back when I still cant fix it haha.

[Homegrownandy]

Additionally,

Just using some test data now and changing a few things. It's the description field that doesn't show the results. all records do have a job_ID

Steve,
I've put in that line. Still no joy. But it does have the job_ID so this was expected as you said.

Ajax,
concatrelated_DWI Is basically a filter it will only show items payable by a department named "DWI" then all other costs will be sent elsewhere. It's probably not the most elegant solution to be honest. This was done over a year ago when I was still pretty new to access.

Lets hope I'm left alone for an hour or so now when I try to fix it! haha.

[Homegrownandy]

Okay thanks for the support.

This is now fixed.. (for no good reason as far as i can see). I was limiting all forms and query's to the selected project using this criteria: [Forms]![background form]![Project].

I moved it from the first concat query to the second one and now all is well. The other group of querys where Im using this function work fine still when they were set up exactly the same.

Thanks again.