concatrelated problem

[spacerobot]

I am trying to use the concatrelated module to combine multiple rows of a field into one comma delimited field with the values in it. I have been over and over this but keep getting the error "too few parameters: expected 1". Can anyone see anything that would be causing this? Not sure what I am doing wrong.

SELECT Project.Code, Project.Descr, ProjectSection.Scope, ProjectType.Descr, Project.CreatedBy, Project.AssignedTo, ProjSecCosts.CostType,
concatrelated("ProjSecCosts.CostType","ProjSecCost s","Code=" & [project].[Code] & " AND [ProjectSection].[SeqNbr]='" & [ProjSecCosts].[ProjSecNbr] & "'") AS Contractors2


FROM ((Project LEFT JOIN ProjectSection ON Project.Code = ProjectSection.Code) LEFT JOIN ProjectType ON Project.ProjectType = ProjectType.Code) LEFT JOIN ProjSecCosts ON (ProjectSection.Code = ProjSecCosts.Code) AND (ProjectSection.SeqNbr = ProjSecCosts.ProjSecNbr)
WHERE (((ProjSecCosts.Group)="Contractors"));



Any help is appreciated. Thanks!

[Perceptus]

Im not familiar with the function you are calling. But the error says you dont have all the required parameters. most likely missing a Comma. Can you paste the related function "Concatrelated".

[spacerobot]

Sorry about that! This is the function I am using:

http://allenbrowne.com/func-concat.html

[Perceptus]

Public Function ConcatRelated(strField As String, _
strTable As String, _
Optional strWhere As String, _
Optional strOrderBy As String, _
Optional strSeparator = ", ") As Variant


You should try using text qualifiers for your table name (2nd Parameter). HAving a space in the table name isnt good unless you wrap the name in []

Like This is Bad
[Like This is good]

SELECT Project.Code, Project.Descr, ProjectSection.Scope, ProjectType.Descr, Project.CreatedBy, Project.AssignedTo, ProjSecCosts.CostType,
concatrelated("ProjSecCosts.CostType","[ProjSecCost s]","Code=" & [project].[Code] & " AND [ProjectSection].[SeqNbr]='" & [ProjSecCosts].[ProjSecNbr] & "'") AS Contractors2


FROM ((Project LEFT JOIN ProjectSection ON Project.Code = ProjectSection.Code) LEFT JOIN ProjectType ON Project.ProjectType = ProjectType.Code) LEFT JOIN ProjSecCosts ON (ProjectSection.Code = ProjSecCosts.Code) AND (ProjectSection.SeqNbr = ProjSecCosts.ProjSecNbr)
WHERE (((ProjSecCosts.Group)="Contractors"));

[CJ_London]

is SeqNbr text? if not, then you need to lose the single quotes

Also, is ProjSecCosts a table or query? if the former or ProjectSection is not in the query then [ProjectSection] in your criteria will fail

[spacerobot]

Thanks for the suggestion. Not sure how that happened but that was a typo. The table name is ProjSecCosts not ProjSecCost s. I am still getting the too few parameters error.

[spacerobot]

is SeqNbr text? if not, then you need to lose the single quotes

Also, is ProjSecCosts a table or query? if the former or ProjectSection is not in the query then [ProjectSection] in your criteria will fail

SeqNbr is an autonumber. And yes ProjSecCosts is a table.

[spacerobot]

I tried removing the single quotes but am still getting the parameter error. Or is it still not correct?

SELECT Project.code, Project.Descr, ProjectSection.Scope, ProjectType.Descr, Project.CreatedBy, Project.AssignedTo, ProjSecCosts.CostType, concatrelated("ProjSecCosts.CostType","[ProjSecCosts]","code=" & [project].[code] & " AND [ProjectSection].[SeqNbr]=" & [ProjSecCosts].[ProjSecNbr]) AS Contractors2
FROM ((Project LEFT JOIN ProjectSection ON Project.code = ProjectSection.code) LEFT JOIN ProjectType ON Project.ProjectType = ProjectType.Code) LEFT JOIN ProjSecCosts ON (ProjectSection.code = ProjSecCosts.code) AND (ProjectSection.SeqNbr = ProjSecCosts.ProjSecNbr)
WHERE (((ProjSecCosts.Group)="Contractors"));

[CJ_London]

SeqNbr is an autonumber

so lose the single quotes which makes it text

And yes ProjSecCosts is a table

So how do you expect the concatrelated function to find [ProjectSection].[SeqNbr]=

[spacerobot]

so lose the single quotes which makes it text
So how do you expect the concatrelated function to find [ProjectSection].[SeqNbr]=

Good question. I didn't really know how to go about referencing other tables. I guess I don't need that table though now that I look at it. If I remove that second condition it works and runs, but returns all groups. What I want to do is limit it to the group called contractors. What would be the correct syntax for that in the concatrelate function? Here is what I am trying, but it is prompting me to enter a parameter for contractors.

concatrelated("ProjSecCosts.CostType","[ProjSecCosts]","mycode=" & [project].[mycode] & " AND [ProjSecCosts].[Group]='" & Contractors & "'") AS Contractors2

[Perceptus]

score one for hueristics

[CJ_London]

1. group is a reserved word so suggest you change it. See this link about the problems in using reserved words and a list of same words
https://support.office.com/en-us/art...ad=US&fromAR=1

2.

What I want to do is limit it to the group called contractors.

if contractors is a value within the group field then you would use

& " AND [ProjSecCosts].[Group]='Contractors'"

strongly recommend you read up on the use of appropriate syntax

https://support.office.com/en-us/art...4-e8dc3e460671

[spacerobot]

1. group is a reserved word so suggest you change it. See this link about the problems in using reserved words and a list of same words
https://support.office.com/en-us/art...ad=US&fromAR=1

2. if contractors is a value within the group field then you would use

& " AND [ProjSecCosts].[Group]='Contractors'"

strongly recommend you read up on the use of appropriate syntax

https://support.office.com/en-us/art...4-e8dc3e460671

Thanks a lot! I was finally able to get it all working.