Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2012
    Posts
    4

    Question Unanswered: How to avoid "Too few parameters, expected 1" error

    Hello,
    I am using MS Access 2010 with an mdb in Access 2000 format.
    I am trying to open a query, which references three additional parameter queries, from VBA. Initially I got a "Too few parameters, expected 1" error. I then attempted setting the Parameters programatically using suggestions from web searches, but was unsuccessful.

    Following are the three Parameter Queries, a related query and finally the query I am trying open, which references the four preceding queries.

    After that is the VBA code that I used to try to solve the problem (unsuccessfully).

    Any suggestions would be greatly appreciated.

    Thanks in advance for any assistance

    Bob Bowman

    NewDisbursementsCnseeQtySumDated
    --------------------------------
    PARAMETERS [Date] DateTime;
    SELECT [New Disbursements].ItemCode, [New Disbursements].CnseeID, Sum([New Disbursements].LocQty) AS NewDisbQtySum
    FROM [New Disbursements]
    WHERE ((([New Disbursements].DisbDate)<=[Date]))
    GROUP BY [New Disbursements].ItemCode, [New Disbursements].CnseeID
    ORDER BY [New Disbursements].ItemCode, [New Disbursements].CnseeID;

    NewSalesCnseeQtySumDated
    ------------------------
    PARAMETERS [Date] DateTime;
    SELECT [New Sales].ItemCode, [New Sales].CnseeID, Sum([New Sales].CnsgnQty) AS NewSalesQtySum
    FROM [New Sales]
    WHERE ((([New Sales].SaleDate)<=[Date]))
    GROUP BY [New Sales].ItemCode, [New Sales].CnseeID
    ORDER BY [New Sales].ItemCode, [New Sales].CnseeID;

    NewReturnsCnseeQtySumDated
    --------------------------
    PARAMETERS [Date] DateTime;
    SELECT [New Returns].ItemCode, [New Returns].CnseeID, Sum([New Returns].CnsgnQty) AS NewReturnsQtySum
    FROM [New Returns]
    WHERE ((([New Returns].ReturnDate)<=[Date]))
    GROUP BY [New Returns].ItemCode, [New Returns].CnseeID
    ORDER BY [New Returns].ItemCode, [New Returns].CnseeID;

    Wine Inventory-CnseeQtyWithNewDisbSalesDated
    --------------------------------------------
    SELECT NewDisbursementsCnseeQtySumDated.ItemCode, NewDisbursementsCnseeQtySumDated.CnseeID, 0 AS Quantity
    FROM NewDisbursementsCnseeQtySumDated LEFT JOIN [CurrWine Inventory-CnseeQty] ON (NewDisbursementsCnseeQtySumDated.CnseeID = [CurrWine Inventory-CnseeQty].CnseeID) AND
    (NewDisbursementsCnseeQtySumDated.ItemCode = [CurrWine Inventory-CnseeQty].ItemCode) WHERE ((([CurrWine Inventory-CnseeQty].CurrInvQty) Is Null))
    UNION
    SELECT NewSalesCnseeQtySumDated.ItemCode, NewSalesCnseeQtySumDated.CnseeID, 0 AS Quantity FROM NewSalesCnseeQtySumDated LEFT JOIN [CurrWine Inventory-CnseeQty] ON
    (NewSalesCnseeQtySumDated.CnseeID = [CurrWine Inventory-CnseeQty].CnseeID) AND (NewSalesCnseeQtySumDated.ItemCode = [CurrWine Inventory-CnseeQty].ItemCode)
    WHERE ((([CurrWine Inventory-CnseeQty].CurrInvQty) Is Null))
    UNION
    SELECT NewReturnsCnseeQtySumDated.ItemCode, NewReturnsCnseeQtySumDated.CnseeID, 0 AS Quantity
    FROM NewReturnsCnseeQtySumDated LEFT JOIN [CurrWine Inventory-CnseeQty] ON (NewReturnsCnseeQtySumDated.CnseeID = [CurrWine Inventory-CnseeQty].CnseeID) AND
    (NewReturnsCnseeQtySumDated.ItemCode = [CurrWine Inventory-CnseeQty].ItemCode)
    WHERE ((([CurrWine Inventory-CnseeQty].CurrInvQty) Is Null))
    UNION
    SELECT [ItemCode], [CnseeID], [CurrInvQty] AS [Quantity]
    FROM [CurrWine Inventory-CnseeQty];

    Current InventoryAndSales-CnseeQtyDated
    ---------------------------------------
    SELECT [Wine Inventory-CnseeQtyWithNewDisbSalesDated].CnseeID, Nz([Quantity])+Nz([NewDisbQtySum],0)-Nz([NewSalesQtySum],0)-Nz([NewReturnsQtySum],0) AS Qty,
    [Wine Inventory-CnseeQtyWithNewDisbSalesDated].ItemCode FROM (([Wine Inventory-CnseeQtyWithNewDisbSalesDated] LEFT JOIN NewDisbursementsCnseeQtySumDated ON
    ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].CnseeID = NewDisbursementsCnseeQtySumDated.CnseeID) AND ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].ItemCode =
    NewDisbursementsCnseeQtySumDated.ItemCode)) LEFT JOIN NewSalesCnseeQtySumDated ON ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].CnseeID = NewSalesCnseeQtySumDated.CnseeID) AND
    ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].ItemCode = NewSalesCnseeQtySumDated.ItemCode)) LEFT JOIN NewReturnsCnseeQtySumDated ON
    ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].CnseeID = NewReturnsCnseeQtySumDated.CnseeID) AND ([Wine Inventory-CnseeQtyWithNewDisbSalesDated].ItemCode =
    NewReturnsCnseeQtySumDated.ItemCode) ORDER BY [Wine Inventory-CnseeQtyWithNewDisbSalesDated].CnseeID;

    VBA code attempting to set the parameter values in the three sub-querys
    -----------------------------------------------------------------------
    Set qdfDisb = CurrentDb.QueryDefs("NewDisbursementsCnseeQtySumDa ted")
    qdfDisb.Parameters(0) = Me.cOFFERDT

    Set qdfSales = CurrentDb.QueryDefs("NewSalesCnseeQtySumDated")
    qdfSales.Parameters(0) = Me.cOFFERDT

    Set qdfRtrns = CurrentDb.QueryDefs("NewReturnsCnseeQtySumDated")
    qdfRtrns.Parameters(0) = Me.cOFFERDT

    Set CurrInvRS = CurrentDb.OpenRecordset("Current InventoryAndSales-CnseeQtyDated")

  2. #2
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Using 'Date' as a fieldname is a bad idea (it's a reserved word) and can cause problems so I would suggest amending that first.

    Below is a link to the reserved word sin Access:
    List of reserved words in Access 2002 and in later versions of Access
    Regards
    JD

    Software-Matters

  3. #3
    Join Date
    Mar 2012
    Posts
    4
    Hello JD,

    I modified the parameter name to [Dt] but got the same results.

    I verified (in the Locals window) that the parameter values in qdfDisb, qdfSales and qdfRtrns were the expected date.

    Any idea what might be tripping me up?

    Thanks,

    Bob Bowman

  4. #4
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    I don't quite understand what the Dt field (formerly [Date]) is and why it needs to be a parameter.
    Regards
    JD

    Software-Matters

  5. #5
    Join Date
    Mar 2012
    Posts
    4
    It needs to be a parameter because the query results must be based on a date that is input by the user. Initially I tried to reference the control on the form in the query (Forms!PostOfferPriceUpdates!cOFFERDT) and that's when I first encountered the "Too few parameters, expected 1" error. I learned from web research that Jet can't interpret form references in a query and that a parameter should work. So far I've had no success in that approach, although the parameter values seem to be set correctly.
    As a test I wrote some code to modify the SQL directly and insert the required date into the WHERE clause. That worked but it's an ugly brute force kluge that I'd rather not be forced to use.

  6. #6
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Sorry but I still don't quite see where the 'Dt' field is coming from. If you want to reference a control on a form then you were going about it the right way with Forms!PostOfferPriceUpdates!cOFFERDT but you then need to set the parameter values something like this:

    Set qdf = CurrentDb.QueryDefs("QueryName")
    qdf.Parameters(0) = [Forms]![PostOfferPriceUpdates]![cOFFERDT ]
    Set rs = qdf.OpenRecordset

    Are you also adding the parameters to the actual query i.e. when in design view choosing Query/parameters form the toolbar (Access2003)?
    Regards
    JD

    Software-Matters

  7. #7
    Join Date
    Mar 2012
    Posts
    4
    JD,

    I guess you didn't look at the SQL for the queries in my first post; of course there's a PARAMETER. And there's also the code that sets the PARAMETERS there; since the code is part of the Form, it's set using Me.cOFFERDT.

    Perhaps you could share this post with Philippa or Robin to see if they have perspective on this problem.

    Bob

  8. #8
    Join Date
    Mar 2009
    Location
    Dorset
    Posts
    119
    Bob,
    Did this used to work in Access2000?
    If you upload the db I can take a closer look.
    Regards
    JD

    Software-Matters

  9. #9
    Join Date
    Jun 2013
    Posts
    1

    Put # around the date

    Hello, I didn't read all of this post but I thought I would offer my suggestion regardless because I had this same error just now.
    the date has to be a #1/1/2012# type date with the hash signs around it.
    Don't hate me if this has already been discussed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •