Results 1 to 2 of 2
  1. #1
    gem1204 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    32

    Setting named paramater for report


    I have an report that uses name paramaters.
    this is the sql for the report
    Code:
    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
    Code:
    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
    Code:
    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
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    Using the field name in your second example should work. Are you sure the listbox is returning the expected value (name vs ID)? The last method should also work, and be just as fast as the original. Either would be more efficient as a WHERE clause. I typically have a single criteria form that can be called from anywhere.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Query Paramater Connection
    By magicmike92 in forum Queries
    Replies: 20
    Last Post: 08-13-2013, 02:57 PM
  2. Removed Enter Paramater Value
    By lsingh165 in forum Access
    Replies: 2
    Last Post: 01-28-2013, 12:52 AM
  3. Replies: 1
    Last Post: 03-23-2012, 01:22 PM
  4. Imported Named Range
    By jgelpi16 in forum Programming
    Replies: 4
    Last Post: 03-04-2011, 10:03 AM
  5. How to import named range from excel
    By timpepu in forum Import/Export Data
    Replies: 1
    Last Post: 02-19-2010, 11:26 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums