2 parameters expected

[Guest]

Hiya

I am running this SQL in VBA and it keeps giving me error: "2 parameters expected

But there is only 1 external reference in the SQL and through the VBA I have taken it out of the string anyway - Could it be because of the left join? Do you know what I might need to change

Code

Set rstColRspns = dbs.OpenRecordset("SELECT IIf([ShortRspns] Is Null,'No Response',[ShortRspns]) AS Rspns "
& "FROM qryAllResponses LEFT JOIN qryRspnsVals ON qryAllResponses.ShortRspns = qryRspnsVals.CompRspns "
& "GROUP BY IIf([ShortRspns] Is Null,'No Response',[ShortRspns]), qryRspnsVals.RspnsVal, qryAllResponses.QstnID "
& "HAVING (((qryAllResponses.QstnID) = " & Forms!frmAnalysis!cboColHead & ")) ORDER BY qryRspnsVals.RspnsVal DESC;"

Thanks

Baz

 

[Allen Browne]

The way you are concatenating the value looks fine, provided QstnID is a
Number type field (not Text), and frmAnalysis is open and cboColHead
contains a valid number.

To find out what is wrong, debug.print the statement, and try pasting it
into SQL view of a new query.

It may be slightly easier to use:
SELECT Nz([ShortRspns], 'No Response') AS Rspns FROM ...

Hope that helps

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am running this SQL in VBA and it keeps giving me error: "2 parameters expected"

But there is only 1 external reference in the SQL and through the VBA I

have taken it out of the string anyway - Could it be because of the left
join? Do you know what I might need to change?

Code:

Set rstColRspns = dbs.OpenRecordset("SELECT IIf([ShortRspns] Is

Null,'No Response',[ShortRspns]) AS Rspns " _

& "FROM qryAllResponses LEFT JOIN qryRspnsVals ON

qryAllResponses.ShortRspns = qryRspnsVals.CompRspns " _

& "GROUP BY IIf([ShortRspns] Is Null,'No Response',[ShortRspns]),

qryRspnsVals.RspnsVal, qryAllResponses.QstnID " _

& "HAVING (((qryAllResponses.QstnID) = " &

Forms!frmAnalysis!cboColHead & ")) ORDER BY qryRspnsVals.RspnsVal DESC;")

 

[Guest]

Thanks for helping me Allen

I figured out that the parameters are in the query that the SQL is based on

Do you know how I can incorporate the parameters into this? I'd rather not use saved queries if possible

This is the updated code

SQLstr = "SELECT IIf([ShortRspns] Is Null,'No Response',[ShortRspns]) AS Rspns "
& "FROM qryAllResponses LEFT JOIN qryRspnsVals ON qryAllResponses.ShortRspns = qryRspnsVals.CompRspns "
& "GROUP BY IIf([ShortRspns] Is Null,'No Response',[ShortRspns]), qryRspnsVals.RspnsVal, qryAllResponses.QstnID "
& "HAVING (((qryAllResponses.QstnID) = " & Forms!frmAnalysis!cboColHead & ")) ORDER BY qryRspnsVals.RspnsVal DESC;

Set dbs = CurrentD
Set rstColRspns = dbs.OpenRecordset(SQLstr

Thanks again Allen

Basi


----- Allen Browne wrote: ----

The way you are concatenating the value looks fine, provided QstnID is
Number type field (not Text), and frmAnalysis is open and cboColHea
contains a valid number

To find out what is wrong, debug.print the statement, and try pasting i
into SQL view of a new query

It may be slightly easier to use
SELECT Nz([ShortRspns], 'No Response') AS Rspns FROM ..

Hope that help

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

have taken it out of the string anyway - Could it be because of the lef
join? Do you know what I might need to change

Code
Set rstColRspns = dbs.OpenRecordset("SELECT IIf([ShortRspns] I

Null,'No Response',[ShortRspns]) AS Rspns "

& "FROM qryAllResponses LEFT JOIN qryRspnsVals O

qryAllResponses.ShortRspns = qryRspnsVals.CompRspns "

& "GROUP BY IIf([ShortRspns] Is Null,'No Response',[ShortRspns])

qryRspnsVals.RspnsVal, qryAllResponses.QstnID "

& "HAVING (((qryAllResponses.QstnID) = "

Forms!frmAnalysis!cboColHead & ")) ORDER BY qryRspnsVals.RspnsVal DESC;"