Help with SQL statement

[wackywoo105]

I have the following SQL generate a query.

SELECT *
FROM RecallLetters
WHERE (((RecallLetters.[Due Date]) Like "*/*/2017" Or (RecallLetters.[Due Date]) Like "*/*/2016" Or (RecallLetters.[Due Date]) Like "*/*/2015") AND ((RecallLetters.PartAdd) In (SELECT [PartAdd] FROM [RecallLetters] As Tmp GROUP BY [PartAdd] HAVING Count(*)>1 )))
ORDER BY RecallLetters.[Due Date];

What I want to add is something like:

OR (RecallLetters.[Due Date]) Like "*/05/2017") AND ((RecallLetters.SMS)=False))

Where the 2 condition have to be met just for this one. However, I can’t get this to be an exclusive statement so the SMS bit ends up applying to all the other parts.

 

[The_Doc_Man]

Maybe hand-massage this just a bit and restructure at least SOME of those parenthetical clauses. Then let's look at it.

SELECT *
    FROM RecallLetters
    WHERE ( [COLOR="Red"](RecallLetters.[Due Date]) Like "*/*/2017") [/COLOR]
        OR (RecallLetters.[Due Date]) Like "*/*/2016") 
        OR (RecallLetters.[Due Date]) Like "*/*/2015") )
        AND (RecallLetters.PartAdd) IN 
              (SELECT [PartAdd] FROM [RecallLetters] As Tmp )
        GROUP BY [PartAdd] HAVING Count(*)>1 ))
        ORDER BY RecallLetters.[Due Date];

If you wanted to add a clause like this:

OR (RecallLetters.[Due Date]) Like "*/05/2017") AND ((RecallLetters.SMS)=False))

your problem is exclusivity. Look at the part I highlighted in red. Then consider an OR between that fragment and the fragment you want to ADD. (You didn't say REPLACE.) There is no way that you would NOT get all 2017 entries if you added the OR sub-clause because the unqualified OR "leg" of that WHERE clause would pull in all records anyway. The qualified leg wouldn't be effective because the unqualified leg is still there.

Two ways to skin the cat here... Simplest is to layer the queries so that the first query (call it QueryA) is then filtered as

SELECT * 
    FROM QueryA 
   WHERE ( [Due Date] NOT LIKE "*/05/2017" ) AND ( [SMS] = TRUE )
   ORDER BY [Due Date] ;

The problem here is that if the Due Date is not unique, filtration is a bit tougher. Therefore, I am not coding up another SUBQUERY as a way to exclude letters. It would be based on a SUBQUERY that selects all the letters you wanted to omit and then uses NOT IN syntax to do the exclusion. If the dates aren't unique, that would not work well anyway.

Two other comments. For a single-source query where the source is named in the FROM clause, you do not have to repeat the source name in the WHERE clause. Second, while it is perfectly legal in Access, we generally do not recommend having spaces embedded in field names. Run the names together and you can then omit the brackets in many cases, which is easier to type.

 

[wackywoo105]

Big thanks for that. I'm a novice at SQL. I actually create the query using access query design view and the copy the sql generated into vba.

I'm using this in another query to pick from the first query. The problem is it omits a person with a date that matches but sms is false, so it is getting rid of all may 17 dates and all sms are false instead of just sms false where the date matches. How can I make it so both conditions have to be met together for the record to be ignored?

    strSQL = "SELECT * FROM RecallLettersDups " & _
             "WHERE ((( RecallLettersDups.[Due Date] not Like " & Chr(34) & "*/" & month1 & "/" & year1 & "" & Chr(34) & _
             ") AND ((RecallLettersDups.SMS) = true )))" & _
             "ORDER BY RecallLettersDups.[Due Date];"
             MsgBox strSQL
    qdf2.SQL = strSQL

 

[wackywoo105]

Ok

SELECT IIf([due date] Like "*/05/2017",IIf([sms]=True,"true","false2"),"false1") AS [Current&SMS], *
FROM RecallLetters
WHERE (((RecallLetters.[Due Date]) Like "*/*/2017" Or (RecallLetters.[Due Date]) Like "*/*/2016" Or (RecallLetters.[Due Date]) Like "*/*/2015") AND ((RecallLetters.PartAdd) In (SELECT [PartAdd] FROM [RecallLetters] As Tmp GROUP BY [PartAdd] HAVING Count(*)>1 )))
ORDER BY RecallLetters.[Due Date];

in the first query produces a field I can select using the second query

SELECT RecallLettersDups.[Title]....
FROM RecallLettersDups
WHERE (((RecallLettersDups.[Current&SMS]) Not Like "true"));

 

[The_Doc_Man]

Yep, sometimes query layering is the easier answer based on old Julius Caesar and his war philosophy of "Divide and Conquer."