Too few parameters. Expected 1

[dowlingm815]

I have a SQL statement to call data using a form. however, it keeps generating a "[3061]: Too few parameters. Expected 1" error.

I've attempted to convert it to queryDef, however, no progress has been made.

Any guidance, would be appreciated.

the original code is as such:

Dim dbs As DAO.Database
Dim rstTemp As DAO.Recordset
Dim strSQL As String
 
Set dbs = CurrentDb()
 
'**** create output table
'Delete temporary table
'DoCmd.RunSQL "DROP TABLE tbl_MktPOsHash;"
 
strSQL = "SELECT FIMSMGR_FTVACCI.FTVACCI_FUND_CODE, " & _
                                "tbl_MktPOs.Date, " & _
                                "tbl_MktPOs.PO, " & _
                                "tbl_MktPOs.POAmt, " & _
                                "tbl_MktPOs.BlnkWvr, " & _
                                "tbl_MktPOs.SC, " & _
                                "tbl_MktPOs.Buyer, " & _
                                "tbl_MktPOs.Desc, " & _
                                "tbl_MktPOs.PO_Originated, " & _
                                "tbl_MktPOs.Index, " & _
                                "tbl_MktPOs.CORD_No, " & _
                                "tbl_MktPOs.BuyerUserName, " & _
                                "tbl_MktPOs.BuyerLastName, " & _
                                "tbl_MktPOs.BuyerFirstName, " & _
                                "tbl_MktPOs.BuyerMkt, " & _
                                "tbl_MktPOs.[Replenishment Order], " & _
                                "tbl_MktPOs.[Contract Name], " & _
                                "tbl_MktPOs.[Contract No], " & _
                                "tbl_MktPOs.[Contract Expiration Date] " & _
        "FROM tbl_MktPOs " & _
        "INNER JOIN FIMSMGR_FTVACCI " & _
        "ON tbl_MktPOs.Index = FIMSMGR_FTVACCI.FTVACCI_ORGN_CODE " & _
        "WHERE (((FIMSMGR_FTVACCI.FTVACCI_FUND_CODE) Like 'R*') AND " & _
                                "((FIMSMGR_FTVACCI.FTVACCI_NCHG_DATE)=#12/31/2099#) AND " & _
                                "((tbl_MktPOs.BlnkWvr) Like 'W' & [Forms]![F_Waiver_Yr]![Txt_Waiver_Yr] & '*B-001'));"
 
Set rstTemp = dbs.OpenRecordset(strSQL)

** Edit **

even if quotes singles are placed around the form value, the query will run but returns no records.

"WHERE (((FIMSMGR_FTVACCI.FTVACCI_FUND_CODE) Like 'R*') AND " & _
                                "((FIMSMGR_FTVACCI.FTVACCI_NCHG_DATE)=#12/31/2099#) AND " & _
                                "((tbl_MktPOs.BlnkWvr) Like 'W' & '[Forms]![F_Waiver_Yr]![Txt_Waiver_Yr]' & '*B-001'));"

[NeoPa]

I assume the line that causes the error message is #37. It's always a good idea to say so though. This then, is a SQL problem. I'm away from my PC ATM so I can't link you to a thread that explains why posting VBA for a SQL problem is not very helpful, but assume I have (I may add it later as it includes other tips to save you time dealing with SQL problems and is worth reviewing anyway).

For now though, I'll just say you need to post the actual SQL you're having issues with rather than the VBA code you create it with. That way we're not doing double the work.

Frankly, I don't see anything obviously causing the issue, but the change from the previous version is an indication that your reference is the cause of your problem. Seeing the actual resultant SQL would be a a lot more useful though, and may answer questions I have about what may be going wrong (It will at least indicate what is used in the value of that control when it goes wrong).

BTW FYI Adding the quotes provided information, but is not remotely part of any solution.

[dowlingm815]

i'm not sure i follow your direction. the issue is on line 36 at the "where" clause when the form parameters are reference.

[NeoPa]

No. Actually it's not. Line #36 is simply an extension of line #11 - an assignment of various values to a string called strSQL. There is nothing wrong with this per se. In effect the only requirement here is that the value is a string. There is only a problem when you try to use the value, then stored in strSQL, in line #37 (where it must be of valid SQL format).

That is where your problem occurs. I expect your error essage would have made that clear (When you choose to debug then that line will be highlighted in yellow). I make mistakes, as we all do, but it would make more sense for you to assume I know what I'm talking about until shown otherwise.

I still need from you what I requested in my earlier post (#2) - The value of strSQL at the point where it is about to be used (Line #37).

[Rabbit]

The problem is how you're referencing the control on the form. You can't reference it from within OpenRecordset. It has to be outside of the quotes.

[dowlingm815]

NeoPa, the value is 12.

Rabbit, the quotes have been removed.

[Rabbit]

I didn't say remove the quotes. I said it needs to be outside of the quotes, i.e. not part of the string at all. But rather pass in the literal value.

[NeoPa]

DowlingM:
NeoPa, the value is 12.

The value of strSQL is a great big long SQL string.

The link I intended to post earlier is How to Debug SQL String, and it seems it would be very much worth your while to check it out.

[NeoPa]

Rabbit:
I didn't say remove the quotes. I said it needs to be outside of the quotes, i.e. not part of the string at all. But rather pass in the literal value.

I'm not sure that expresses your thinking very clearly Rabbit. If I don't misunderstand you then you are suggesting that the reference to the control be replaced with a literal value representing the control, which is added into the calculation of strSQL outside of the other literal strings. Something like :

strSQL = "SELECT ... WHERE [X] = " & Me.Control

When the control is set at 12 this would result in the string :

"SELECT ... WHERE [X] = 12"

This string value would then be passed to the SQL engine to be processed. This would also work of course, but control references are possible in SQL.

[Rabbit]

When I tried it with OpenRecordset, referencing the control within the string resulted in the same error.

[NeoPa]

I assume you're referring to the first version (without the reference in quotes). You don't say which level of string you had in mind. Tell me if I misunderstand you.

I just tried a very simple test in a Query and ran :

SELECT Forms!frmClipboard.txtCategory

while the form [frmClipboard] was open and the TextBox [txtCategory] (of the currently selected record) held the value "Link". This is exactly what I got as a single record result.

I just rechecked in exactly the same way but with :

SELECT Forms!frmClipboard!txtCategory

It didn't work.

NeoPa:
This would also work of course, but control references are possible in SQL.

I didn't say the reference was correct, just that references are possible. I felt that was something we would better look into when the OP had posted the information (SQL string) properly (at which point I would have done this testing to verify exactly what did and didn't work). I didn't feel it appropriate to go to that level with only VBA to work with.

[Rabbit]

I'm guessing you tried it in a query def? That should work. Try it in OpenRecordset, it stops working. I don't know why but it seems to render things differently when you use it in a query def as opposed to OpenRecordset.

[NeoPa]

It seems bizarre, but you're absolutely correct (Not bizarre that you're correct, but bizarre what you're correct about)!

I tried various flavours, but that error message certainly indicates that the reference is not recognised. I don't believe there are any potentially viable forms of the reference I didn't try - but all to no avail. It certainly seems that such references are invalid when used within the OpenRecordset() method (although the Help system gives no clues of such inconsistent behaviour either).

@DowlingM
This confirms what Rabbit suggested in post #5 (clarified further in my post #9). Let us know if this is enough to get you going with.

[Stewart Ross]

Access has always had a bit of a split personality when it comes to interpreting form controls as parameters.

This link to a SmartAccess article for A2003 on the MS site below provides a good explanation of why the error occurs. In summary, the Jet database engine is invoked in two different contexts. When invoked from code it appears that the user interface is not invoked by Jet to resolve the missing parameter, which is otherwise done automatically when running from the usual forms/reports/queries interface.

MS's knowledgebase articles, like the SmartAccess article, suggest setting the missing form parameter by appending to a suitable qdef in code (see this knowledgebase article for example).

-Stewart