Report with multiple where conditions (1 Viewer)

jsdba · Oct 17, 2018

Hi,

I'm building a dynamic where clause for my report based on some selections made on a form by users. I cant get the syntax right.

I have 2 combo boxes cboPM1 and cboPM2 and i need to build the following criteria
e.g.
((EmpID = cboPM1 AND StatusID = 1) OR (rEmpID = cboPM1 AND StatusID = 1))
or
((EmpID = cboPM2 AND StatusID = 1) OR (rEmpID = cboPM2 and StatusID = 1))

I'm trying to evaluate 4 different clauses. Please help!

plog · Oct 17, 2018

Reports don't have WHERE clauses. Queries do--and I think that's where you are going wrong.

What I would do is strip out the criteria relating to your combo boxes from your query (that means leaving StatusID=1 in there since it has nothing to do with your form). Then use DoCmd.OpenReport (https://docs.microsoft.com/en-us/office/vba/api/access.docmd.openreport) to filter your report.

The user would set the drop downs, then click a button. The button would compile a filter string based on the data in the combo boxes and then use it when you call DoCmd.OpenReport

plog · Oct 17, 2018

As for what you are doing wrong---you aren't referencing your form elements correctly. This is how it should be:

(EmpID = Forms!YourFormNameHere!cboPm1)

You have to let it know its a form, the name of your form and then your element name.

isladogs · Oct 17, 2018

Plog is absolutely correct for code run from the report.

However, if this code is being run in the form you can use the Me. syntax
EmpID=Me.cboPm1

jsdba · Oct 17, 2018

Hi guys thanks for the reply but i probably didn't state my problem correctly.

Plog: I know there is no "where clause" in a report i meant to say WhereCondition. im building a string strCriteria that im passing to Docmd.OpenReport. the e.g. i gave is not the code im using, its just to help u understand an the permutations im trying to cover in the where condition.

What i didn't say (i didn't it it was essential) is that StatusID is determined by a options group is not always 1, i can be any number but i use the options group to pass the number so i don't have to pass it a variable.

I can probably figure a way to pass the user filter directly to the underlying query by referencing the form in the query but i don't want to go that route yet.

Ultimately im just looking for the right syntax for strCriteria:

Code:

strCriteria = "((EmpID = cboPM1 AND StatusID = 1) OR (rEmpID = cboPM1 AND StatusID = 1)) 
OR 
((EmpID = cboPM2 AND StatusID = 1) OR (rEmpID = cboPM2 and StatusID = 1))"
DoCmd.OpenReport "rptReport", acViewReport, , strCriteria

plog · Oct 17, 2018

If StatusId is not always 1, then why are you not using a variable? Also, you can use algebraic replacement to extract it out of every condition:

Code:

(A AND Status=1) Or (B AND Status=1) OR (C AND Status=1) OR (D AND Status=1)

is equivalent to: 

(A Or B OR C) AND (Status=1)

On to the issue:

"(EmpID = cboPM1)..."

That means the field EmpID of your report is equal to the cboPM1 field of your report. My guess is the report doesn't have a cboPM1 field, my guess is cboPM1 is a variable, in which case you reference its value like so:

"(EmpID = " & cboPM1 & ")..."

And if cboPM1 is an element on a form you reference it like I mentioned in my first post.

jsdba · Oct 17, 2018

plog said:
If StatusId is not always 1, then why are you not using a variable? Also, you can use algebraic replacement to extract it out of every condition:

Code:

[B](A AND Status=1) Or (B AND Status=1) OR (C AND Status=1) OR (D AND Status=1) is equivalent to: (A Or B OR C) AND (Status=1)[/B]

On to the issue:

"(EmpID = cboPM1)..."

That means the field EmpID of your report is equal to the cboPM1 field of your report. My guess is the report doesn't have a cboPM1 field, my guess is cboPM1 is a variable, in which case you reference its value like so:

"(EmpID = " & cboPM1 & ")..."

And if cboPM1 is an element on a form you reference it like I mentioned in my first post.

Now we're on the right track. Let me take a stab at it.

jsdba · Oct 18, 2018

I need a little help with the syntax. Im gettin a complile error: syntax error. I suspect its the "AND" part of the code. Im trying to evaluate 2 conditions see code below

Code:

strCriteria = ([B]([/B]"EmpID=" & Nz(cboProjectManager1, 0) & " OR rEmpID=" & Nz(cboProjectManager1, 0) & " OR EmpID=" & Nz(cboProjectManager2, 0) & " OR rEmpID=" & Nz(cboProjectManager2, 0) & "[B])[/B] And [B]([/B]StatusID = 3[B])[/B])

plog · Oct 18, 2018

You bit off more than you can chew. Start smaller, get it working with just 1 condition then add more until you have it all. A few hints:

You have 9 double quote marks--they usually work in pairs.
Why do you have characters outside quote marks at the beginning and ending of your string?

jsdba · Oct 18, 2018

plog said:
You bit off more than you can chew. Start smaller, get it working with just 1 condition then add more until you have it all. A few hints:

You have 9 double quote marks--they usually work in pairs.
Why do you have characters outside quote marks at the beginning and ending of your string?

I can make everything work but for adding the "AND statusID" part. Im at an absolute lost trying to using parenthesis. this what im trying to do

Code:

(A Or B OR C) AND (Status=1)

. if i dont use parenthesis it would look like this

Code:

(A Or B OR C AND Status=1)

and that wont evaluate my conditions properly

plog · Oct 18, 2018

Show me the code right before the step where it won't work.

jsdba · Oct 18, 2018

plog said:
Show me the code right before the step where it won't work.

this works

Code:

strCriteria = "EmpID=" & Nz(cboProjectManager1, 0) & " OR rEmpID=" & Nz(cboProjectManager1, 0) & " OR EmpID=" & Nz(cboProjectManager2, 0) & " OR rEmpID=" & Nz(cboProjectManager2, 0)

but how do i add "AND StatusID = 1" to it?

plog · Oct 18, 2018

So lets call all of that D and the StatusID=1 E. This is what it should be:

strCriteria = "(" & D & ") AND (E)"

All of the A conditions work together, so they must be inside parenthesis, which must be inside quote marks because they aren't variables. The D works by itself as well so it must be inside parenthesis which are inside quote marks.

Your initial attempt had stuff outside quote marks and didn't have ending quotes.

jsdba · Oct 18, 2018

plog said:
So lets call all of that D and the StatusID=1 E. This is what it should be:

strCriteria = "(" & D & ") AND (E)"

All of the A conditions work together, so they must be inside parenthesis, which must be inside quote marks because they aren't variables. The D works by itself as well so it must be inside parenthesis which are inside quote marks.

Your initial attempt had stuff outside quote marks and didn't have ending quotes.

Final product:

Code:

strCriteria = "(StatusID > 3) And " & ("EmpID=" & Nz(cboProjectManager1, 0) & " OR rEmpID=" & Nz(cboProjectManager1, 0) & " OR EmpID=" & Nz(cboProjectManager2, 0) & " OR rEmpID=" & Nz(cboProjectManager2, 0))

Report with multiple where conditions (1 Viewer)

jsdba

Registered User.

plog

Banishment Pending

plog

Banishment Pending

isladogs

MVP / VIP

jsdba

Registered User.

plog

Banishment Pending

jsdba

Registered User.

jsdba

Registered User.

plog

Banishment Pending

jsdba

Registered User.

plog

Banishment Pending

jsdba

Registered User.

plog

Banishment Pending

jsdba

Registered User.

Similar threads

Users who are viewing this thread