OpenReport with filter and where

This should work, assuming Employee Number is numeric:

 DoCmd.OpenReport "YourReportName", acViewPreview, , "[EmployeeNumber] = & Me.txtEmpNumber"

mx

typo:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[EmployeeNumber] = " & Me.txtEmpNumber

If EmployeeNumber is Text, then use this syntax:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[EmployeeNumber] = " & Chr(34) & Me.txtEmpNumber & Chr(34)

mx

if [EmployeeNumber] is actually a number then it should look something like:

docmd.OpenReport "reportname", acViewPreview, , "[EmployeeNumber] = " & me.txtEmpNumber

I hate it when he does that!  ;-)

Can't get it to work.
Yes I am working with numbers but i did try both examples and the Access generated "Enter Parameter Value" dialog box continues to pop-up.

Tried:
DoCmd.openReport "ThisWeekPayReport", acViewPreview, , "[EmployeeNumber] = " & Me.txtEmpNumber

DoCmd.OpenReport "ThisWeekPayReport", acViewPreview, , "[EmployeeNumber] = " & Chr(34) & Me.txtEmpNumber & Chr(34)


All of this is simply to try and catch any error on the EmployeeNumber value. If I enter the same number into the Access dialog box the report will open.

what parameter value did it ask for?

if it is asking for [EmployeeNumber] it means that the report does not contain that field in its underlying datasource.  If it is asking for another parameter, it means that the query you are using as the RecordSource for the report has a parameter defined or in the criteria of the SQL statement.

Yes ... the syntax is correct.  Possibly you have a typo or [EmployeeNumber] is not a *visible* field in that query ?

mx

What version of Access are you using?  

If 2007, you may need to look to see whether the [EmployeeNumber] field is in the query, but not on the report?  If so, add it to the report and set its visible property to false (if you don't want to see it).

Access 2010

The data source of the report is a query based on a table with a column/field named lngEmployeeID. In the query I placed an expression - Employee Number:[EmployeeNumber] . Then in the lngEmployeeID field I used [EmployeeNumber] as the criteria.

So if I open the query the Access dialog box opens asking for the EmployeeNumber. I enter a number and the query is filtered to that one employee.

In a small form I have a text box and the user will type in there Employee Number and click an OK button. This button runs the DoCmd to open the report with the value of the textbox.

I'm not sure if I'm making this more difficult that it should be.

The problem is the alias - which has a space.  The criteria is fine, since it references the real field name w/o a space.

Employee Number:[EmployeeNumber]

but if you have a parameter in the query that is the underlying recordsource for the report, you are always going to be asked to enter the value for that parameter, even if you provide a value in the WHERE argument of the OpenReport method.

I think fyed is correct.
I tried:
DoCmd.openReport "ThisWeekPayReport", acViewPreview, , "[Employee Number] = " & Me.txtEmpNumber
and it continued to ask for the EmployeeNumber.

I removed the alias and the criteria and tried:
Docmd.OpenReport "ThisWeekPayReport", acViewPreview, , "[lngEmployeeID] = " & me.txtEmpNumber
and it worked.

Thanks to all for the help

I must have misunderstood what you said then.  I should have asked to see the SQL for the query.

mx