none
Why is my query not filtering this particular field?! RRS feed

  • 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")) 
    

    Monday, August 28, 2017 12:17 PM

Answers

  • 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.

    • Marked as answer by InnVis Monday, August 28, 2017 1:39 PM
    Monday, August 28, 2017 1:21 PM

All replies

  • 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.

    Monday, August 28, 2017 12:27 PM
  • 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
    Monday, August 28, 2017 12:32 PM
  • 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.

    Monday, August 28, 2017 12:37 PM
  • 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"));
    

    Monday, August 28, 2017 12:41 PM
  • 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.

    • Marked as answer by InnVis Monday, August 28, 2017 1:39 PM
    Monday, August 28, 2017 1:21 PM
  • Thanks! This worked, finally. lol
    Monday, August 28, 2017 1:39 PM