Setting report paramaters (1 Viewer)

[gem1204]

I have an report that uses name paramaters.

this is the sql for the report

[COLOR="Red"][B]PARAMETERS [whatCompany] Text ( 255 );[/B][/COLOR]
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

 

[pbaldy]

As answered elsewhere:

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.