Too Few Parameters Expected 1 (sub - sub query in VBA)

[SOS]

This is a little confusing to me. I've this problem for work. I have a query that has a parameter on it. The value is provided by a form (combo box for a year) and the field has a long integer datatype.

This query is actually a sub, sub query for this one that builds a table of invoices (I didn't build this code, just have to work with it), and this query gathers invoice values based on the PeriodYear and has been hand changed each year to less than the current year. So, manually someone goes in and changes it to < 2010 and next year < 2011, etc.

I am trying to use this combo box to select the year. However, the query that gets the value works fine if you run it by itself. Or if you run the next query up the line where this one is a subquery in and then the next one up the line (top level) which is fine if done manually. If I run it using code like:

Set rstInvoice = cDB.OpenRecordset("SELECT * FROM " & mstrQueryName & " WHERE MGPOrgFK Is Not Null ORDER BY Property_ID", _
dbOpenSnapshot)

Then it bombs, and says Too Few Parameters Expected 1.

So, I've tried using this:

Set qdf = CurrentDb.QueryDefs("qryInvoiceDetail")
 
qdf.Parameters("PeriodYear") = "< " & Forms!frmQSRMain.cboInvoiceYear
 
 
Set cDB = CurrentDb
Set rstInvoiceTable = cDB.OpenRecordset(mstrTableName, dbOpenDynaset)

But it bombs out giving me the error message -

Error 3421 Data Type Conversion Error

If I change it to this:

qdf.Parameters("PeriodYear") = Forms!frmQSRMain.cboInvoiceYear

Then I still get the Too Few Parameters Expected 1 error.

So, I'm not sure how to make the change to pass the parameter to the sub, sub query and still call the main query.

 

[vbaInet]

Hey SOS, so the sub sub query is parameterised? First thing you might need to do is to declare the parameter in the Paramters property of the query.

The less than operator you're passing to the parameter will be read as string and not as an operator and also you mentioned the field is a String so it will bomb anyway.

I don't think you will be able to pass the parameter to the subquery because it isn't part of the main query's parameters.

Have you considered amending the sql of the querydef instead?

 

[SOS]

Have you considered amending the sql of the querydef instead?

Hey, good idea. I'll give that a shot here. Thanks.

 

[Banana]

Hard to tell since I don't know the SQL for all queries involved, but a thought regarding subqueries and the error - it can happen whenever one try to reference a column that is expected to be present in the table but isn't actually selected by the sub query so there's no columns for the outer query to select/join/filter/order on and that "column" is then interpreted by Access to be a parameter rather than an actual column.

HTH.

 

[SOS]

Hey, good idea. I'll give that a shot here. Thanks.

Worked like a charm.

By the way for anyone who finds this and has a similar issue, I am using the SQL Tools provided for free by Access MVP Armen Stein. You can get the tools here.

I have modifed the code to this then:

Dim cDB As DAO.Database
Dim fld As DAO.Field
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim intNum As Integer
Dim strWhere As String
 
On Error GoTo Errs
 
strWhere = "WHERE (((dbo_wselCashDistAnnual.PeriodYear) < " & [Forms]![frmQSRMain]![cboInvoiceYear] & ") AND ((dbo_wselCashDistAnnual.Current_Expected_Amount)>0) AND ((dbo_wselCashDistPriorities.Designation) = 'Fee to LP' Or (dbo_wselCashDistPriorities.Designation) Like 'CF Split to LP*') AND ((dbo_wselCashDistAnnual.Actual_Amount) Is Null Or (dbo_wselCashDistAnnual.Actual_Amount)<>[Current_Expected_Amount]));"
 
Set qdf = CurrentDb.QueryDefs("qryInvoiceDetail")
 
strSQL = qdf.SQL
 
qdf.SQL = [B]ReplaceWhereClause[/B](strSQL, strWhere)
 
qdf.Close
 
Set cDB = CurrentDb
 
Set rstInvoiceTable = cDB.OpenRecordset(mstrTableName, dbOpenDynaset)
 
Set rstInvoice = cDB.OpenRecordset("SELECT * FROM " & mstrQueryName & " WHERE MGPOrgFK Is Not Null ORDER BY Property_ID", _
        dbOpenSnapshot)

 

[vbaInet]

Grand!!!

You forgot to include the link for Stein's tool by the way.

Just one thing though, are you not going to put the qdf back to its original state after making the amendment? Or that doesn't matter in your case?

 

[SOS]

Grand!!!

You forgot to include the link for Stein's tool by the way.

Yeah, I realized that and went back and edited it.

Just one thing though, are you not going to put the qdf back to its original state after making the amendment? Or that doesn't matter in your case?

for this it doesn't matter because it will always be run from the same place and the criteria will always be set to the current value of the combo box. But good thing to let others know - if you want to reset you need to save the original SQL string

Dim strOrig As String
 
strOrig = qdf.SQL
 
...then do the stuff
 
qdf.SQL = strOrig
qdf.Close
Set qdf = Nothing