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?

Gary SamuelsPlant ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
This should work, assuming Employee Number is numeric:

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

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
typo:

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

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

mx
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Dale FyeOwner, Developing Solutions LLCCommented:

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

docmd.OpenReport "reportname", acViewPreview, , "[EmployeeNumber] = " & me.txtEmpNumber
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 Architect / Systems AnalystCommented:
Yes ... the syntax is correct.  Possibly you have a typo or [EmployeeNumber] is not a *visible* field in that query ?

mx
0
Dale FyeOwner, Developing Solutions LLCCommented:
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 Architect / Systems AnalystCommented:
Employee Number:[EmployeeNumber]

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

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


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 FyeOwner, Developing Solutions LLCCommented:
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 Architect / Systems AnalystCommented:
I must have misunderstood what you said then.  I should have asked to see the SQL for the query.

mx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.