4 Query Groupings

[access2010]

Could I please receive assistance with this Query with 4 criteria’s?

If Stock_Name “Is Not Null”
And if Currency is "CAD$"
and or if Trade is "Purchased"
and or if Investigate is "Yes"
Then all of these matching records will be printed.

Thank you.
Nicole

 

[CJ_London]

what have you tried?

You need to be more explicit about your ‘and or’s’

Ands and ors need to be clarified with brackets in the same as in maths

So do you mean

a and ( b or c)

Or perhaps

(A and b) or c

 

[plog]

You've got 18 records in Investments01_tbl. Which records should the query show? Please give me the Investment_ID value of the records the query should show.

 

[Pat Hartman]

Where (Stock_Name Is Not Null And Currency = "CAD$")
Or Trade = "Purchased"
Or Investigate is "Yes"

There is no such conditional as and/or so please rewrite the expression using parentheses to specify how the condition will be evaluated. The above "solution" is unlikely to be correct.

Here is another possibility

Where (Stock_Name Is Not Null And Currency = "CAD$")
And (Trade = "Purchased" Or Investigate is "Yes")

Also, if Investigate is boolean, you need to use True or False rather than "Yes" or "No"

 

[access2010]

what have you tried?

You need to be more explicit about your ‘and or’s’

Ands and ors need to be clarified with brackets in the same as in maths

So do you mean

a and ( b or c)

Or perhaps

(A and b) or c

 

[access2010]

Thank you for your question CJ, below is what I have tried.

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Currency, Investments01_tbl.Trade, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Stock_Name) Is Not Null)) OR (((Investments01_tbl.Currency)="CAD$")) OR (((Investments01_tbl.Trade)="Purchased")) OR (((Investments01_tbl.Investigate)="Yes"))
ORDER BY Investments01_tbl.Stock_Name;

We would like to print all the records for all “CAS$” marked stocks either if trade is PURCHASED or if Investigate is YES, or if marked with both codes.
Therefore all Stocks which are marked Purchased or marked Yes will be printed if the Currency is CAD$.

Nicole

 

[access2010]

You've got 18 records in Investments01_tbl. Which records should the query show? Please give me the Investment_ID value of the records the query should show.

Thank you for your question Plog

We would like to print a report for all Stocks which are either marked Purchased or marked Yes, if the Currency is CAD$.

Crystal

 

[access2010]

Where (Stock_Name Is Not Null And Currency = "CAD$")
Or Trade = "Purchased"
Or Investigate is "Yes"

There is no such conditional as and/or so please rewrite the expression using parentheses to specify how the condition will be evaluated. The above "solution" is unlikely to be correct.

Here is another possibility

Where (Stock_Name Is Not Null And Currency = "CAD$")
And (Trade = "Purchased" Or Investigate is "Yes")

Also, if Investigate is boolean, you need to use True or False rather than "Yes" or "No"

Thank you Professor Pat for your suggestion and the code below seems to work.

SELECT Investments01_tbl.Stock_Name, Investments01_tbl.Currency, Investments01_tbl.Trade, Investments01_tbl.Investigate
FROM Investments01_tbl
WHERE (((Investments01_tbl.Currency)="CAD$")) and (((Investments01_tbl.Trade)="Purchased" OR Investigate="Yes"))
ORDER BY Investments01_tbl.Stock_Name;
Crystal

 

[The_Doc_Man]

From a strict standpoint of logic implementation, the VBA "OR" relational operator IS the equivalent to the English language "and/or" construct because a simple "OR" is an inclusive OR. It allows for one or both options to be true. By contrast, there IS such a thing as XOR (exclusive or) that would mean "one or the other but NOT both."

 

[Pat Hartman]

WHERE (Investments01_tbl.Stock_Name Is Not Null AND Investments01_tbl.Currency ="CAD$") AND (Investments01_tbl.Trade ="Purchased"  OR Investments01_tbl.Investigate ="Yes")

If you wanted to eliminate the stock name condition, you would write the expression this way:

WHERE Investments01_tbl.Currency ="CAD$" AND (Investments01_tbl.Trade ="Purchased"  OR Investments01_tbl.Investigate ="Yes")

 

[Isaac]

Ideally when you ask a question with numerous booleans and where order of operation is paramount, include parenthesis and maybe even formatting like indents to make it crystal clear what the groupings and order of operations are