ConcatRelated Error: "Error 3061: Too few parameters. Expected 3" (1 Viewer)

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
Hi -- need some help ASAP!!! Also, sorry for the long post... if you're looking for the main code/issue please skip to the bottom of the post.

I've read an endless number of forums relating to this issue over the last week trying to figure out my problem, but have not had any luck applying the solution to my situation.

I am trying to use Allen Browne's ConcatRelated function (found here: http://allenbrowne.com/func-concat.html) to concatenate a list of values from my query into one row. I keep gettting the syntax error "Error 3061: Too few parameters. Expected 3".

I have tried all the different variations of [], '', "", but nothing has worked. I almost feel that the way I have set things up just will not work with this function - so I will give some context on my environment.

I built a form "Pass Percentage Form" to filter down the results of a previous query, "Pivoted Data". This form is used generate the criteria for a new query "Pivoted_Data_Query". The form uses 3 cascading combo boxes to essentially set the WHERE clause of the results of the "Pivoted_Data_Query". The code for this query is as follows:

Code:
SELECT [Pivoted Data].[Process], [Pivoted Data].[Control ID] AS ControlID, Round(Avg([Pivoted Data].[Pass]),2)*100 AS [Pass %], [Pivoted Data].[ASD]
FROM [Pivoted Data]
WHERE ((([Pivoted Data].Place)=Forms![Pass Percentage Form]!Place_combo) And (([Pivoted Data].Test)=Forms![Pass Percentage Form]!Test_combo) And (([Pivoted Data].[Testing Date])=Forms![Pass Percentage Form]!Testing_combo))
GROUP BY [Pivoted Data].[Process], [Pivoted Data].[Control ID], [Pivoted Data].Place, [Pivoted Data].Test, [Pivoted Data].[Testing Date], [Pivoted Data].[ASD]
ORDER BY [Pivoted Data].[Control ID];

Now, I am getting the right information, but rather than it display results like:
Control1, ASD1, Score
Control1, ASD2, Score

I want to display them like:
Control1, "ASD1, ASD2" , Score


Hence, where Allen Browne's function comes into play. So I tried to create a new query (which is working off of another query, which is working off of a form -- is this an issue?) to implement this code, and this is where I get the "Too few parameters" error. Is this the best way to do this, or is there a way I can implement the function in the first query?

Code:
Code:
SELECT DISTINCT [Pivoted_Data_Query].[ControlID], [Pivoted_Data_Query].[Pass %], [Pivoted_Data_Query].[Process], ConcatRelated("KSD","Pivoted_Data_Query","[ControlID] ='" & [ControlID] & "'") AS ASDs
FROM [Pivoted_Data_Query];

Any help would be IMMENSELY appreciated - I am stuck and on a tight deadline. I know this is probably very confusing, so I apologize, but I just started working with Access two weeks ago. This is probably too high-level for me but I am determined to figure it out!!
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

I think the problem is with the parameters in your query. You could try converting them into TempVars, or you might be able to use SQL with this simple function. Good luck!
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
Hi. Welcome to AWF!

I think the problem is with the parameters in your query. You could try converting them into TempVars, or you might be able to use SQL with this simple function. Good luck!

Thanks! And thanks for the reply. Are you referring to converting the parameters set by the form in the first query?
--
Also I tried using the simple function earlier today but could not find out how/where to implement.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
Thanks! And thanks for the reply. Are you referring to converting the parameters set by the form in the first query?
--
Also I tried using the simple function earlier today but could not find out how/where to implement.
Hi. Yes, try replacing your parameters to use TempVars instead of a Form. To use the SimpleCSV() function, I was thinking you could use a SQL statement, if possible, to get your concatenated values. For example:

SQL:
SELECT FieldName, SimpleCSV("SELECT FieldName FROM ActualTableName WHERE SomeCriteriaToMatchTheCurrentRecord") As ConcatString
FROM PivotQueryName
Just a thought...
 

Micron

AWF VIP
Local time
Today, 14:23
Joined
Oct 20, 2018
Messages
3,476
If I read that right, you're querying a query (aka a stacked query), and the one being called is the one with the form control references.
Then I'm fairly certain that the issue is that the second query, which opens first, is seeing those 3 fields and can't relate to the references. I believe the answer is to go to design in the 1st query (the one being called) and define the parameters there (right click on query background where tables are located) and choose 'parameters'. Someone here or in another forum had this same problem about 3 days ago and that was the solution. Hopefully I recall it correctly; if not, then it must have been the calling query that needed the parameters.
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
Hi. Yes, try replacing your parameters to use TempVars instead of a Form. To use the SimpleCSV() function, I was thinking you could use a SQL statement, if possible, to get your concatenated values. For example:

SQL:
SELECT FieldName, SimpleCSV("SELECT FieldName FROM ActualTableName WHERE SomeCriteriaToMatchTheCurrentRecord") As ConcatString
FROM PivotQueryName
Just a thought...
I'll look into that - the reason that we are using a form though is because we want to display the results of the query in a nice format which we hthought a form would be best form. So, is there a way to implement while still using the form?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
I'll look into that - the reason that we are using a form though is because we want to display the results of the query in a nice format which we hthought a form would be best form. So, is there a way to implement while still using the form?
Yes, you should still be able to use a form.
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
If I read that right, you're querying a query (aka a stacked query), and the one being called is the one with the form control references.
Then I'm fairly certain that the issue is that the second query, which opens first, is seeing those 3 fields and can't relate to the references. I believe the answer is to go to design in the 1st query (the one being called) and define the parameters there (right click on query background where tables are located) and choose 'parameters'. Someone here or in another forum had this same problem about 3 days ago and that was the solution. Hopefully I recall it correctly; if not, then it must have been the calling query that needed the parameters.
Yes that sounds right! Basically have a form that is setting the WHERE clause for the initial query, and then created a second query to essentially use Allen Browne's function to concatenate the results from the first query.

Okay I'll look into the parameters of the initial query - is there a specific thing I'm supposed to change them to? Or could you post the link to the solution you are referring to? Thanks!!
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
The same way you use one now. How are you using it now? Can you show us?
It is sensitive information, so I don't think I can post it unfortunately. If I didn't explain my use of it well enough in the original post, what else can I tell you that might help?

Sorry for the inconvenience!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
It is sensitive information, so I don't think I can post it unfortunately. If I didn't explain my use of it well enough in the original post, what else can I tell you that might help?

Sorry for the inconvenience!
And I can't really give you any direction either without knowing much about what you're trying to do. Information might be sensitive, but the form may not be. Either that, or use "test/dummy" data in your examples. The more information you can give us, the better focus our suggestions become. Otherwise, give it a try and let us know how it goes. Like I said, it shouldn't be any different than what you're already doing with your form. A form can have a Record Source of either a table or a query.
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
And I can't really give you any direction either without knowing much about what you're trying to do. Information might be sensitive, but the form may not be. Either that, or use "test/dummy" data in your examples. The more information you can give us, the better focus our suggestions become. Otherwise, give it a try and let us know how it goes. Like I said, it shouldn't be any different than what you're already doing with your form. A form can have a Record Source of either a table or a query.

I'm trying to display the results of my form with the concat function. My form's recordsource is currently the "Pivoted_Data_Query" so it displays the results of this query using the combo box filters on the form to set the where clause of the query- I would like to concat the results of this query, so my thought was to create a new query based off of the existing query and implement the function there, and then change the record source of the form to the new query.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
I'm trying to display the results of my form with the concat function. My form's recordsource is currently the "Pivoted_Data_Query" so it displays the results of this query using the combo box filters on the form to set the where clause of the query- I would like to concat the results of this query, so my thought was to create a new query based off of the existing query and implement the function there, and then change the record source of the form to the new query.
Hi. In that case, you don't need to create a new query. If you use the SimpleCSV() function, you can just add it as the Control Source of an unbound Textbox on your form.
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
Hi. In that case, you don't need to create a new query. If you use the SimpleCSV() function, you can just add it as the Control Source of an unbound Textbox on your form.
Oh great!

Here is the code I am using for the text box and getting an error:
SELECT DISTINCT ControlID, SimpleCSV("SELECT ASD FROM Pivoted_Data_Query WHERE ControlID=" & [ControlID]) AS ASDs
FROM Pivoted_Data_Query

I am trying to group by ControlID, which is a short text field.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
Oh great!

Here is the code I am using for the text box and getting an error:
SELECT DISTINCT ControlID, SimpleCSV("SELECT ASD FROM Pivoted_Data_Query WHERE ControlID=" & [ControlID]) AS ASDs
FROM Pivoted_Data_Query

I am trying to group by ControlID, which is a short text field.
Try changing that to this:

Code:
=SimpleCSV("SELECT ASD FROM Pivoted_Data_Query WHERE ControlID=" & [ControlID])
 

newdbguy

Member
Local time
Today, 14:23
Joined
Mar 9, 2020
Messages
30
Try changing that to this:

Code:
=SimpleCSV("SELECT ASD FROM Pivoted_Data_Query WHERE ControlID=" & [ControlID])
Switched it to that and did not see an error in Design View, but when I switched to Form View I got a #Name? error for each record, with the same amount of rows as before ):
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
Switched it to that and did not see an error in Design View, but when I switched to Form View I got a #Name? error for each record, with the same amount of rows as before ):
Hi. A #Name? error usually means a typo. Please double check the names of your objects. You could test the function in the Immediate Window. For example:

?SimpleCSV("SELECT ASD FROM Pivot_Data_Query WHERE ControlID=1")
 

theDBguy

I’m here to help
Staff member
Local time
Today, 11:23
Joined
Oct 29, 2018
Messages
21,358
Hi. By the way, since I have no way of testing your scenario, I was thinking maybe this information might help as well. Unfortunately, it would mean modifying Allen's or my code to use it.

 

Cronk

Registered User.
Local time
Tomorrow, 05:23
Joined
Jul 4, 2013
Messages
2,770
?SimpleCSV("SELECT ASD FROM Pivot_Data_Query WHERE ControlID=1")

That is not going to give an output as stated was required in #1, ie
Control1, ASD1,ASD2 , Score

(Presumably the Score value is the sum of all the individual ASDx scores.)
 

Users who are viewing this thread

Top Bottom