Why is my query not filtering this particular field?!

Question

Hey guys,

I have this query that I'm trying to filter to EXCLUDE the selection of "CIV" AND "CONTRACT" from the field "Branch". Here's the SQL.

SELECT [Rank] & " " & [Last Name] & ", " & [First Name] AS [Contact Name], tblContacts.Day, tblContacts.[OFF], tblContacts.Night, tblContacts.T, tblContacts.L, tblContacts.S, tblContacts.SIQ, tblContacts.UA, tblContacts.D, tblContacts.[RETURN DATE], tblContacts.[PERSTAT NOTES], tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Service, tblContacts.Rank, tblContacts.Branch
FROM tblContacts
WHERE ((Not (tblContacts.[Last Name]) Is Null)) OR ((Not (tblContacts.[First Name]) Is Null)) AND ((Not (tblContacts.[Branch])="CIV" or "CONTRACT")) 

Answer 1

Hey guys,

I have this query that I'm trying to filter to EXCLUDE the selection of "CIV" AND "CONTRACT" from the field "Branch". Here's the SQL.

SELECT [Rank] & " " & [Last Name] & ", " & [First Name] AS [Contact Name], tblContacts.Day, tblContacts.[OFF], tblContacts.Night, tblContacts.T, tblContacts.L, tblContacts.S, tblContacts.SIQ, tblContacts.UA, tblContacts.D, tblContacts.[RETURN DATE], tblContacts.[PERSTAT NOTES], tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Service, tblContacts.Rank, tblContacts.Branch
FROM tblContacts
WHERE ((Not (tblContacts.[Last Name]) Is Null)) OR ((Not (tblContacts.[First Name]) Is Null)) AND ((Not (tblContacts.[Branch])="CIV" or "CONTRACT")) 

Hi Jamie,

You can try:

... AND (tblContacts.Branch <> "CIV") _ 
  & AND (tblContacts.Branch <> "CONTRACT")

Imb.

Answer 2

Hey Imb, I'm sorry if I seem a bit clueless, but it won't let me save the query SQL in this format. Keeps flagging the _ and if I remove the _ then it flags the second AND

Answer 3

Hey Imb, I'm sorry if I seem a bit clueless, but it won't let me save the query SQL in this format. Keeps flagging the _ and if I remove the _ then it flags the second AND

Hi Jamie,

The _ is the character in VBA to indicate that the line continues on the next line. I am so used to that, because I never use Querydef, but only VBA.

You can remove the _ so that the whole sql-part is on one line, and that you substitue in your whole sql-string.

Imb.

Answer 4

I went ahead and did this, but it doesn't filter the two fields. I'm very confused.

SELECT [Rank] & " " & [Last Name] & ", " & [First Name] AS [Contact Name], tblContacts.Day, tblContacts.[OFF], tblContacts.Night, tblContacts.T, tblContacts.L, tblContacts.S, tblContacts.SIQ, tblContacts.UA, tblContacts.D, tblContacts.[RETURN DATE], tblContacts.[PERSTAT NOTES], tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Service, tblContacts.Rank, tblContacts.Branch
FROM tblContacts
WHERE ((Not (tblContacts.[Last Name]) Is Null)) OR ((Not (tblContacts.[First Name]) Is Null)) AND ((tblContacts.[Branch] <>"CIV")) & ((tblContacts.[Branch]<>"CONTRACTS"));

Answer 5

I went ahead and did this, but it doesn't filter the two fields. I'm very confused.

SELECT [Rank] & " " & [Last Name] & ", " & [First Name] AS [Contact Name], tblContacts.Day, tblContacts.[OFF], tblContacts.Night, tblContacts.T, tblContacts.L, tblContacts.S, tblContacts.SIQ, tblContacts.UA, tblContacts.D, tblContacts.[RETURN DATE], tblContacts.[PERSTAT NOTES], tblContacts.[Last Name], tblContacts.[First Name], tblContacts.Service, tblContacts.Rank, tblContacts.Branch
FROM tblContacts
WHERE ((Not (tblContacts.[Last Name]) Is Null)) OR ((Not (tblContacts.[First Name]) Is Null)) AND ((tblContacts.[Branch] <>"CIV")) & ((tblContacts.[Branch]<>"CONTRACTS"));

Hi Jamie,

The & in the line should be an " AND ". The & character glues the strings on the different lines together.

You could also try:

WHERE ((tblContacts.[Last Name] > "") OR (tblContacts.[First Name] > "")) AND (tblContacts.[Branch] <> "CIV") AND (tblContacts.[Branch] <> "CONTRACTS");

Imb.

Answer 6

Thanks! This worked, finally. lol