CODEWITHSUNDEEP
sqlvbams-accessnested
Cavanaugh
Cavanaugh

Reputation: 1

Nested And/Or in VBA SQL query

I have an Access UDA that uses some VBA/SQL code that I put together a long time ago and someone has recently asked me to adjust a particular query to exclude two criteria. I thought I could simply add a SQL AND OR statement but I've been hitting quite a bit of trouble trying to get this simple fix to work.

My original code:

SELECT * FROM [Master List] 
WHERE ([Master List].IB)='" & strIBTO & "' AND ([Master List].[Type])<>'N'"

This correctly returns the results of the specific passed variable "strIBTO" that does not include Type = "N".

Essentially the ask is to also exclude another Type from the query, so I modified the code:

SELECT * FROM [Master List] 
WHERE ([Master List].IB)='" & strIBTO & "' AND ([Master List].[Type])<>'N' OR ([Master List].[Type])<>'T'"

Now it seems like Access is throwing out my entire AND statement, as I am getting results with both "N" and "T" in them. Tried various permutations with parenthesis enclosing the entire AND statement and inside as well but can't get the right results.

Is VBA SQL different in nesting AND/OR from MySQL? What am I doing wrong?

Upvotes: 0

Views: 3059

Answers (3)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11181

This is elementary boolean algebra!

[Master List].[Type]<>'N' OR [Master List].[Type]<>'T' will always return entire set!

Think opposite: [Master List].[Type]='N' OR [Master List].[Type]='T' would return Ns more Ts from set.

Negation to this is:

[Master List].[Type]<>'N' AND [Master List].[Type]<>'T'

Upvotes: 0

Johnny Bones
Johnny Bones

Reputation: 8404

It looks to me like you're not bracketing properly. Replace this:

WHERE ([Master List].IB)='" & strIBTO & "' AND ([Master List].[Type])<>'N' OR ([Master List].[Type])<>'T'"

With this:

WHERE ([Master List].IB)='" & strIBTO & "' AND (([Master List].[Type])<>'N' AND ([Master List].[Type])<>'T')

Once you bracket properly, you have to change that OR to an AND as I have done above.

Upvotes: 0

DeanG
DeanG

Reputation: 647

If I understand correctly, you are also trying to exclude type 'T' in addition to type 'N'. There are a few options including, but not limited to :

SELECT * FROM [Master List] 
WHERE ([Master List].IB)='" & strIBTO & "'
AND ([Master List].[Type])<>'N'
AND ([Master List].[Type])<>'T'

or (easier to update again)

SELECT * FROM [Master List] 
WHERE ([Master List].IB)='" & strIBTO & "'
AND ([Master List].[Type]) NOT IN ('N', 'T')

Upvotes: 2

Related Questions