Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts

    Access 2010 Query Filters - "Too few parameters. Expected 7"

    I have an Access 2010 Database I use for trading commodities.


    From my primary Table ([Tbl Evaluations and Trades]) I have a Query that summarizes commodities into markets and works perfectly with Forms and Reports.


    The Query ([Qry Markets, Num Profits&Losses Summary]) in SQL:


    SELECT Left([Instrument_Description],InStr([instrument_Description]," ")) AS Markets, Sum(IIf((IIf(IsNull([Profit_A]),0,[Profit_A])+IIf(IsNull([Profit_B]),0,[Profit_B])+IIf(IsNull([Profit_C]),0,[Profit_C])+IIf(IsNull([Profit_D]),0,[Profit_D]))>=0,1,0)) AS NumProfits, Sum(IIf((IIf(IsNull([Profit_A]),0,[Profit_A])+IIf(IsNull([Profit_B]),0,[Profit_B])+IIf(IsNull([Profit_C]),0,[Profit_C])+IIf(IsNull([Profit_D]),0,[Profit_D]))<0,1,0)) AS NumLosses
    FROM [Tbl Evaluations and Trades]
    WHERE ((([Tbl Evaluations and Trades].Instrument_Description) Like "*" & [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Description] & "*")
    AND (([Tbl Evaluations and Trades].Instrument) Like [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Code] & "*")
    AND (([Tbl Evaluations and Trades].Instrument_Type) Like [Forms]![Fm Trade Report Selector].[Tbx_Instrument_Type] & "*")
    AND (([Tbl Evaluations and Trades].Broker_Name) Like [Forms]![Fm Trade Report Selector].[Tbx_Broker_Name] & "*")
    AND ((IIf([C1_Date]>[C_Date],[C1_Date],[C_Date])) Between [Forms]![Fm Trade Report Selector].[Tbx_Period_Start]
    And [Forms]![Fm Trade Report Selector].[Tbx_Period_End])
    AND (([Tbl Evaluations and Trades].Trade_Type) Like [Forms]![Fm Trade Report Selector].[Tbx_Trade_Type] & "*")
    AND (([Tbl Evaluations and Trades].Trade_Entry_Price_A)>0))
    GROUP BY Left([Instrument_Description],InStr([instrument_Description]," "))
    ORDER BY Left([Instrument_Description],InStr([instrument_Description]," "));


    The Query result table before Grouping in "Markets" looks like this:


    And after Grouping:



    You will note that this Query has Filters set from values extracted [Forms]![Fm Trade Report Selector]


    When I try to call this Query from the Report Private Sub Report_Activate() event to write the results from the Query to an Excel file I get the Error Message: "Too few parameters. Expected 7". I'm using the following code to write the Query results to the Excel file when a Report is opened:


    Private Sub Report_Activate()


    ' strTQName: is the name of the table or query you want to send to Excel
    ' strPathExcelFilename: is the path to and name of the Excel wookbook you want to send it to
    ' strSheetName: is the name of the sheet you want to send it to

    Dim strTQName As String
    Dim strPathExcelFilename As String
    Dim strSheetName As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim ApXL As Object
    Dim xlWBk As Object
    Dim xlWSh As Object
    Dim fld As DAO.Field
    Dim strPath As String

    Const xlCenter As Long = -4108
    Const xlBottom As Long = -4107

    On Error GoTo err_handler


    strTQName = "Qry Markets, Num Profits&Losses Summary"
    strPathExcelFilename = "C:\Users\My Name\Documents\Trading the Market\Trading Database\Markets, Number of Profits & Losses.xlsx"
    strSheetName = "Data_Import"


    Set rst = CurrentDb.OpenRecordset(strTQName)
    Set ApXL = CreateObject("Excel.Application")
    Set xlWBk = ApXL.Workbooks.Open(strPathExcelFilename)
    ApXL.Visible = True

    Set xlWSh = xlWBk.Worksheets(strSheetName)

    xlWSh.Activate
    xlWSh.Range("A1").Select

    For Each fld In rst.Fields
    ApXL.ActiveCell = fld.Name
    ApXL.ActiveCell.Offset(0, 1).Select
    Next
    rst.MoveFirst

    xlWSh.Range("A2").CopyFromRecordset rst
    xlWSh.Range("1:1").Select


    rst.Close
    Set rst = Nothing

    Exit Sub

    err_handler:
    DoCmd.SetWarnings True
    MsgBox Err.Description, vbExclamation, Err.Number
    Exit Sub


    End Sub


    From what I can work out, this process is stalling at the line "Set rst = CurrentDb.OpenRecordset(strTQName)"


    It seems to me that I need to somehow transfer the filter parameters from [Forms]![Fm Trade Report Selector] to the instance of the Query to be processed in the above mentioned line. My question therefore is "How?"


    Why am I wanting to do this? So I can create a fancy Chart in Excel to import back into an Access Report.


    Any bright ideas gratefully accepted.


    Cheers

  2. #2
    2 Star Lounger
    Join Date
    Dec 2002
    Location
    Bruges, Belgium
    Posts
    122
    Thanks
    14
    Thanked 2 Times in 2 Posts
    You must add a Parameter object before executing the query. Examples of how to do so can be found at:
    VBA,ADO connection an query parameters
    or
    How to invoke a parameterized query using VBA

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    When you use a reference to a form in a query, and then try to use that query in a form or report you frequently run into this error. This can usually be solved by using the Eval() function. For example, your query contained this:

    Between [Forms]![Fm Trade Report Selector].[Tbx_Period_Start] And [Forms]![Fm Trade Report Selector].[Tbx_Period_End]

    You need to rewrite it like this:

    Between Eval("[Forms]![Fm Trade Report Selector].[Tbx_Period_Start]") And Eval("[Forms]![Fm Trade Report Selector].[Tbx_Period_End")

    (Note the use of the quote marks, they are essential.)
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. The Following User Says Thank You to MarkLiquorman For This Useful Post:

    BygAuldByrd (2015-02-02)

  5. #4
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Bogangar NSW Australia
    Posts
    154
    Thanks
    29
    Thanked 3 Times in 2 Posts
    Thanks Mark,

    Your simple suggestion worked just fine.

    I put the Eval("...") statement in the Query that referenced the Query (strTQName).

    Cheers

Posting Permissions

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