• ACCESS 2007 Null and criteria query

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » ACCESS 2007 Null and criteria query

    Author
    Topic
    Goatfarmer98
    AskWoody Lounger
    February 3, 2017 at 12:40 pm #508116

    I want to filter out records without an email address on a specific criteria of classification. The classification works fine but I also get both null and not null records.

    Reply | Quote
    Viewing 2 reply threads
    Author
    Replies
    • RetiredGeek
      AskWoody_MVP
      February 3, 2017 at 12:55 pm #1590420

      G.F.

      Is it correct that you have a field called Classification and you want to ONLY filter out records with a blank email address for a specific value of the Classification field?

      If that is the case you need a two row query grid.
      On the first row you’ll set the Classification field formula to The excluded classification value
      On the second row you’ll set the Classification field formula to = The excluded classification value and the Email field to “”

      Of course you include all other fields the same in both lines.
      Should do the trick as this is an OR query and will select records that conform to either of the two rows.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      Reply | Quote
      • Goatfarmer98
        AskWoody Lounger
        February 3, 2017 at 2:12 pm #1590424

        Actually Mr. Retired, a beer right now sounds mighty good!

        My query contains the 2 fields “Email1” and “Citizen/Leg” I need to work with.

        I want only the fields with an Email address and those records with “SFL”, or “YAL” or “C” or “F” in the “citizen/leg” field.

        Citizen/leg is the first field in the query immediately followed by Email1.

        I get the “SFL” and the other 3 criteria records when I have no Email1 criteria, alone with blank and filled Email1 fields (image retired1.)

        However, when I put “not null” on the first criteria row of Email1 and beginning with “SFL” on the second row of Citizen/Leg criteria, followed beneath it the remaining 3 other criteria on subsequent rows, I get the other files beneath Citizen/leg including my “H”ouse and “S”enate legislators’ records in database view and all of the emails – with or without data.(image retired.2)

        Thank you very much for your patience. You wouldn’t have to twist my arm to bring us a couple of beers out of Auburn, Alabama area.

        46619-retired.146620-retired.2

        Either field at this time is “sorted” and both checked to “show.”

        Reply | Quote
      • Goatfarmer98
        AskWoody Lounger
        February 3, 2017 at 2:14 pm #1590425

        After moving the position of email1 and citizen/leg from 1 to 2nd and back again through trial and error, my message does not match up with their placement in the images.

        Reply | Quote
    • RetiredGeek
      AskWoody_MVP
      February 3, 2017 at 3:50 pm #1590426

      G.F.,

      Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.

      You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.

      Access evaluates each of the criteria rows only by the criteria that exist on that row.

      HTH :cheers:

      May the Forces of good computing be with you!

      RG

      PowerShell & VBA Rule!
      Computer Specs

      Reply | Quote
      • Goatfarmer98
        AskWoody Lounger
        February 3, 2017 at 4:19 pm #1590427

        You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are? Will now have that beer. Might have two, one for the each of us. Thank you VERY much.

        G.F.,

        Ok here’s where I think your problem is. The first line in your query will match All Citizen/Leg values! So they are all returned.

        You need to move one of the values probably “F” since that will be the easiest to get rid of in the list. Next you need to repeat the IS NOT NULL on each of the rows.

        Access evaluates each of the criteria rows only by the criteria that exist on that row.

        HTH :cheers:

        Reply | Quote
        • RetiredGeek
          AskWoody_MVP
          February 3, 2017 at 7:43 pm #1590430

          You rock Mr. Retired. How do I acknowledge your solution with a “star” or “1” as you are?

          Just click the thumbs up icon on the post that helped you, it’s located at the bottom left of the post.

          Glad to have been of assistance.

          :cheers:

          May the Forces of good computing be with you!

          RG

          PowerShell & VBA Rule!
          Computer Specs

          Reply | Quote
    • WSMarkLiquorman
      AskWoody Lounger
      February 3, 2017 at 9:47 pm #1590432

      As I see it, the WHERE clause of your SQL should be:
      WHERE [Email1] Is Not Null AND [Citizen/Leg] IN (“SFL”, “YAL”, “C”,”F”)

      This says “I want to return all records that have an email value and that have 1 of 4 acceptable values for citizen/leg”. This assumes that the Email1 field can not contain a zero-length string. If it can, then use: WHERE [Email1] & “” “” AND …

      On a separate issue, you should get out of the habit of using special characters in your fieldnames, with the exception of the underscore character.

      Reply | Quote
    Viewing 2 reply threads
    Reply To: ACCESS 2007 Null and criteria query

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    April 2025
    S M T W T F S
     12345
    6789101112
    13141516171819
    20212223242526
    27282930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.