• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

OpenReport with filter and where

I have an Access report based on a query named 'qryThisWeekTimeCard'.
The qryThisWeekTimeCard has one criteria named 'EmployeeNumber'.

I have a form  in which I would like to use txtEmpNumber.value as the [EmployeeNumber] and open the report. What is the correct DoCmd.OpenReport syntax?

0
Gary Samuels
Asked:
Gary Samuels
  • 7
  • 6
  • 3
2 Solutions
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
This should work, assuming Employee Number is numeric:

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

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
typo:

DoCmd.OpenReport "YourReportName", acViewPreview, , "[EmployeeNumber] = " & Me.txtEmpNumber
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If EmployeeNumber is Text, then use this syntax:

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

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:

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

docmd.OpenReport "reportname", acViewPreview, , "[EmployeeNumber] = " & me.txtEmpNumber
0
 
Dale FyeCommented:
I hate it when he does that!  ;-)
0
 
Gary SamuelsPlant ManagerAuthor Commented:
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.
0
 
Dale FyeCommented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Yes ... the syntax is correct.  Possibly you have a typo or [EmployeeNumber] is not a *visible* field in that query ?

mx
0
 
Dale FyeCommented:
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).
0
 
Gary SamuelsPlant ManagerAuthor Commented:
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.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Employee Number:[EmployeeNumber]

DoCmd.openReport "ThisWeekPayReport", acViewPreview, , "[Employee Number] = " & Me.txtEmpNumber
                                   you need a Space here ...........................................^
0
 
Dale FyeCommented:
Get rid of the [EmployeeNumber] criteria in the reports RecordSource SQL.  Then use:

Docmd.OpenReport "yourReportName", acViewPreview, , "[lngEmployeeID] = " & me.txtEmpNumber


0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
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]
0
 
Dale FyeCommented:
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.
0
 
Gary SamuelsPlant ManagerAuthor Commented:
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
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
I must have misunderstood what you said then.  I should have asked to see the SQL for the query.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 7
  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now