How do I adjust a Where statement in Macro Builder in Access 2010 to select the correct data?

I created a form and on that form is a textbox called tbxJobNum. I enter a Job Number into that box and then click the Submit button in the bottom of the form. The Submit button has a macro built on it that I want to pass the JobNumber from the tbxJobNum box to the Report that it is opening. I can only assume that my where condition is currently incorrect as I'm not getting any records to return.

The current Where clause is
 [Reports]![rptForm1]![jpJobNum]=[Forms]![frmFAI]![tbxJobNum] Or [Reports]![rptForm1]![jhJobNum]=[Forms]![frmFAI]![tbxJobNum]

Open in new window


I want this clause to function as I have the criteria set up in the query originally as pictured below.
 Query Criteria
Thanks in advance for the help!
LVL 2
Jarred MeyerProduction ManagerAsked:
Who is Participating?
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What else does your Macro do? If all it does is open the report, then you might consider using the Openreport method instead:

Docmd.OpenReport "YourReportName", acViewPreview, , "jpJobNum=" & Me.tbxJobNum & " OR jhJobNum=" & Me.tbxJobNum

If jbJobNum and jhJobNum are Text fields:

Docmd.OpenReport "YourReportName", acViewPreview, , "jpJobNum='" & Me.tbxJobNum & "' OR jhJobNum='" & Me.tbxJobNum & "'"

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
Jeffrey CoachmanMIS LiasonCommented:
Are you sure you have data that meets the criteria?

Does the macro work with only one field's criteria?

0
Jarred MeyerProduction ManagerAuthor Commented:
LSM - I tried your formula for text fields and am not getting that to work..  Here is a screen shot of what I've got.. I removed one of the criteria to see if at least one is working and it is not.. I've managed to get the parameter I'm putting into tbxJobNum to transfer over to report (I tested this by added a textbox to the report that returns the input data) There is just something wrong with the formula.

boag2000: I'm using the Openreport command within the macro builder on the button and trying to add the tbxJobNum input data to the Where clause as part of the OpenReport command. Here is a screenshot of the macrobuilder..


SP32-20111209-071709.jpg
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.

Jarred MeyerProduction ManagerAuthor Commented:
[jpJobNum]=[Forms]![frmFAI]![tbxJobNum] Or [jhJobNum]=[Forms]![frmFAI]![tbxJobNum]

I got it!

This formula does the job!
0
Jarred MeyerProduction ManagerAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for SeyerIT's comment http:/Q_27485165.html#37261755

for the following reason:

I figured it out:)
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Hmmm ... it would seem that my suggestion provided you with the correct syntax and therefore some assistance, but if you don't feel that it did, then I'm okay with the disposition. Glad you got it resolved.
0
Jarred MeyerProduction ManagerAuthor Commented:
Add assisted response from LSM.
0
Jarred MeyerProduction ManagerAuthor Commented:
Sorry LSM.. I didn't even realize the order of both formulas was the same.. Just the syntax was different..

Thanks for the help!
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.

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.