CODEWITHSUNDEEP
sqlms-access
Chuck0185
Chuck0185

Reputation: 531

MS Access SQL Issue with OR and AND Operators

The goal of the query at the bottom is to update the FullComplDate field if the below requirements are satisfied. I am not sure if it is an issue with the arrangement of the statements or parenthesis but I've tried changing the order of the statements and adding parenthesis but no luck.

BOTH of these statements are true:

  1. tblSubscribers.FullComplDate Is Null
  2. tblSubscribers.SubscrComplDate Is Not Null

AND either one of these two statements are true:

  1. tblSubscribers.CoverageLevel="Employee Only"))
  2. tblSubscribers.CoverageLevel="Employee + Child(ren)"

Instead, what is happening is that any record that satisfies that first three statements is being updated AND any record the satisfies just the 4th statement is being updated. 

UPDATE tblSubscribers SET tblSubscribers.FullComplDate = Format(Date(),"mmmm" & " " & "yyyy")
WHERE (((tblSubscribers.FullComplDate) Is Null) AND ((tblSubscribers.SubscrComplDate) Is Not Null) AND (((tblSubscribers.CoverageLevel)="Employee Only")) OR (((tblSubscribers.CoverageLevel)="Employee + Child(ren)")));

Upvotes: 0

Views: 72

Answers (2)

HereGoes
HereGoes

Reputation: 1320

I think the issues is with the parenthesis.... if you recude them and only have them wrapping the OR condition it should work better.

UPDATE tblSubscribers SET tblSubscribers.FullComplDate = Format(Date(),"mmmm" & " " & "yyyy")
WHERE tblSubscribers.FullComplDate Is Null
AND tblSubscribers.SubscrComplDate Is Not Null
AND (tblSubscribers.CoverageLevel="Employee Only" OR tblSubscribers.CoverageLevel ="Employee + Child(ren)")

You could try the IN Clause to avoid the OR

UPDATE tblSubscribers SET tblSubscribers.FullComplDate = Format(Date(),"mmmm" & " " & "yyyy")
WHERE tblSubscribers.FullComplDate Is Null
AND tblSubscribers.SubscrComplDate Is Not Null
AND tblSubscribers.CoverageLevel IN ("Employee Only" ,"Employee + Child(ren)")

Upvotes: 1

Kev Ritchie
Kev Ritchie

Reputation: 1647

The following should work (note brackets wrapping last AND/OR):

UPDATE      tblSubscribers 
SET         tblSubscribers.FullComplDate = Format(Date(),"mmmm" & " " & "yyyy")
WHERE       tblSubscribers.FullComplDate Is Null
AND         tblSubscribers.SubscrComplDate Is Not Null
AND         (tblSubscribers.CoverageLevel = "Employee Only"
OR          tblSubscribers.CoverageLevel = "Employee + Child(ren)")

Upvotes: 0

Related Questions