Run Time Error 3061 Too few Parameters

[rjmdt]

Hi Folks,

I have a function that is giving a Run Time Error 3061 Too Few Parameters.

Following is the Function:

Function Concatenate(pstrSQL As String)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0)
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
Set db = Nothing
Concatenate = strConcat
End Function


I am calling the function from the following Query:

SELECT allergentype.Formkey, Concatenate("SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= " & [allergentype]![Formkey]) AS Allergens
FROM allergentype;

allergentype is a query that follows:

SELECT FMMAST.Formkey, FMITEM.Itemkey, INMOEL.Occupexposurelimits
FROM (FMMAST INNER JOIN FMITEM ON FMMAST.Formkey = FMITEM.Formkey) LEFT JOIN INMOEL ON FMITEM.Itemkey = INMOEL.Itemkey
GROUP BY FMMAST.Formkey, FMITEM.Itemkey, INMOEL.Occupexposurelimits
HAVING (((INMOEL.Occupexposurelimits) Is Not Null));

The error is occuring at the following point in the function:

Set rs = db.OpenRecordset(pstrSQL)

The value of pstrSQL = "SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= OW10105-R1 "

What is causing this error? What can I do?

Thanks in advance

 

[dhookom]

You are close but since your Formkey field is text, you must delimit it with quotes:

SELECT allergentype.Formkey, Concatenate("SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= """ & [allergentype]![Formkey] & """") AS Allergens
FROM allergentype;

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rjmdt]

Thanks,

The function is giving me a result!

The problem is the result is not correct. I need the result to have formkey and a single value of the child records (allergentype). The resulting string is the only the first record of formkey.

Example of allergentype:

Formkey Itemkey Occupexposurelimits
OW10105-R1 R27011 3
OW10106 R24005 4
OW10106 R25008-A 2
PL01100 R24010 2
PL01200 R27008 3
PL01200 R27012 3
PL01300 R25003 2
PL01300 R27008 3
R2157-05-JRB R24004 2
R2157-05-JRB R25010 2
R2157-05-JRB R25017 2

I want the results to be:
Formkey Occupexposurelimits
OW10105-R1 3
OW10106 42
PL01100 2
PL01200 3
PL01300 23
R2157-05-JRB 2

I thought the concatenate the multiple child records into a single value would accomplish this.

What should I do from here?

Thanks

 

[dhookom]

Try

SELECT Formkey, Concatenate("SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= """ & [Formkey] & """", " ") AS Allergens
FROM allergentype
GROUP BY FormKey, Concatenate("SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= """ & [Formkey] & """", " ");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rjmdt]

Gives an error:

Wrong number of arguements used with function in query Concatenate("SELECT [Occupexposurelimits] FROM allergentype WHERE Formkey= """ & [Formkey] & """", " ")

 

[dhookom]

I thought the code was copied from my Concatenate function which allows the developer to specify the delimiter between "child" values.

Remove the comma and second argument [red], " "[/red] from the Concatenate() expression.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]