Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Weatherford TX (Between Fort Worth and Abilene)

    Unanswered: Set named paramater for report

    I have an report that uses name paramaters.

    this is the sql for the report
    PARAMETERS [whatCompany] Text ( 255 );
    SELECT tblInvoices.ClientCompany, tblInvoices_Details.Charge, Sum(tblInvoices_Details.Hours)
     AS SumOfHours, tblInvoices.InvoiceID
    FROM tblInvoices INNER JOIN tblInvoices_Details ON tblInvoices.InvoiceID = tblInvoices_Details.InvoiceID
    GROUP BY tblInvoices.ClientCompany, tblInvoices_Details.Charge, tblInvoices.InvoiceID
    HAVING (((tblInvoices.ClientCompany)=[whatCompany]));
    How do I pass the paramaters to the report? I've tried several different ways but can't get it to work
    Dim stdocname As String
    Dim stLink As String
    stdocname = "RptWithParm"
    stLink = "ClientCompany = " & "'" & Me.lstCustomer & "'" 'Using the field name doesn't work
    DoCmd.OpenReport stdocname, acViewReport, , stLink
    'When I try to set the value of the paramater that doesn't work either
    stLink = "[whatCompany] = " & "'" & Me.lstCustomer & "'" 'using the paramater name doesn't work
    DoCmd.OpenReport stdocname, acViewReport, , stLink
    I know I could use the value of the form in the criteria like this
    HAVING (((tblInvoices.ClientCompany)=[Forms]![frmTesRptParm]![lstCustomer]));
    If I use the list box as the criteria I want to be able to use reports in other than one place, plus there are over 80,000 records and it'll run faster if I set the criteria before the report opens instead of setting a filter after it opens to only show up to about 100.

    Can someone help me with this?

    BTW - I using MS Access 2010

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    well you could pass the company as part of the reports onopen event filter

    usually you are better off using another table to constrain the company to ensure data consistency.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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