Improve on a open command filter

I have the following opening a report using MS Access 2003....

DoCmd.OpenReport "rGeneralShort", PrintOption, , "(((tMain.OutLab)=""PSL"") AND (((tMain.Status)= ""In Progress"") or ((tMain.Status)= ""Submitted""))) or (((tMain.OutLab)=""PSL"") AND (((tMain.Status)= ""In Progress"") or ((tMain.Status)= ""Submitted"") or ((tMain.Status)= ""Pending Lenses"")) AND((tMain.OutLab)=""PSL"") AND ((tMain.LensSupplier)= ""Nassau"") AND IsNull(((tMain.DateLens))))"

I would like to add the following to the filter:
tMain.LNotes Like '*HOT*' OR tMain.PNotes Like '*HOT*'

I can't get the syntax correct and believe its redundant as the main criteria that all the others fall under is tMain.OutLab="PSL"

How can I add the additional filter to the filter and I think it can be stream lined a bit too.

Thank you.
thandelAsked:
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.

Jeffrey CoachmanMIS LiasonCommented:
If this were me, I would do all of that filtering in a query.
Then base the report on the query, ...
Then all you have to do is open the report...

as far as your issue here, ...it seems like you need to take a careful look at your nesting...
I am sure another expert will be along to assist.

Jeff
thandelAuthor Commented:
Thanks Jeff, call me silly but I like to code over a query... just my style.  I'm trying to get better at it.  Hopefully someone with more experience will see something obvious.
thandelAuthor Commented:
(someone with more experience that I have)
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!

John TsioumprisSoftware & Systems EngineerCommented:
Putting such amount of code straight  on a Docmd is a bit strange...
It would be better to have your criteria at least on a separate string properly formatted that can be easily modified
Dim strCriteria as string
strCriteria = vbnullstring
strCriteria =strCriteria & " (((tMain.OutLab)=""PSL"") AND (((tMain.Status)= ""In Progress"") OR "
strCriteria =strCriteria & " " ((tMain.Status)= ""Submitted""))) OR (((tMain.OutLab)=""PSL"") AND "
strCriteria =strCriteria & " (((tMain.Status)= ""In Progress"") OR((tMain.Status)= ""Submitted"") OR "
strCriteria =strCriteria & " ((tMain.Status)= ""Pending Lenses"")) AND((tMain.OutLab)=""PSL"") AND " 
strCriteria =strCriteria & " ((tMain.LensSupplier)= ""Nassau"") AND IsNull(((tMain.DateLens))))"

Open in new window

Dale FyeOwner, Developing Solutions LLCCommented:
I always build my criteria strings as separate statements, which allows me to print them out to determine precisely what Access sees when it opens the report.  I also try to group my criteria and eliminate extra parenthesis when possible.  Furthermore, I try to simplify the query as much as I can to make it easier to read.  In this case, I think you have complicated the query slightly.  One way to do this is to structure the criteria so that you can see where groupings begin and end.   I think you could go with:

strCriteria = "(tMain.OutLab='PSL') AND " _
            & "(" _
            & "(tMain.Status IN ('In Progress', 'Submitted')) OR " _
            & "(tMain.Status = 'Pending Lenses') AND (tMain.LensSupplier= 'Nassau') AND (tMain.DateLens IS NULL)" _
            & ") AND " _
            & "((tMain.LNotes Like '*HOT*') OR (tMain.PNotes Like '*HOT*'))"
debug.print strCriteria
DoCmd.OpenReport "rGeneralShort", PrintOption, , strCriteria

Open in new window

I'm not entirely sure whether this new criteria about the notes fields applies to the rest of the criteria or not, but used an AND in this case.

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
thandelAuthor Commented:
Thanks for the improvement in the filter.  I just had trouble following all the parenthesis... this helped understand it better as well.
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, when I look at it now, I think it is missing another pair of parenthesis, try:

strCriteria = "(tMain.OutLab='PSL') AND " _
            & "(" _
            & "(tMain.Status IN ('In Progress', 'Submitted')) OR " _
            & "(" _
            & "(tMain.Status = 'Pending Lenses') AND (tMain.LensSupplier= 'Nassau') AND (tMain.DateLens IS NULL)" _
            & ")" _
            & ") AND " _
            & "((tMain.LNotes Like '*HOT*') OR (tMain.PNotes Like '*HOT*'))"
debug.print strCriteria
DoCmd.OpenReport "rGeneralShort", PrintOption, , strCriteria

Open in new window

thandelAuthor Commented:
Well unless I made a typo from your accepted solution I had it working... thanks for the followup.
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, not that I look at it again, the original should work correctly.  

I was confusing the precedence of logical operators, thinking that the OR would be processed before the ANDs, but that is not correct.  By adding the parentheses in the second post I merely explicitly defined a logical precedence which was already being used by the compiler.

Sorry for any confusion.
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.