Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    I am batting my head against a wall here, and getting REAL frustrated. I'm sure that I'm doing something wrong, but can't figure out what that is!!

    I have a personnel database (ACCESS 2007). Field 1 has a person's name in it, field 2 has an attribute of that person. Field 2 can be blank (nothing ever entered in it). Field 3 has the person's residence address.

    I'm trying to build another Field in a Query that will be TRUE when Field 2 is blank, such as NewField: = ([Field 2] is null). That doesn't work, so how about trying NewField: = ([Field 2] =""). Turns out that doesn't work either.

    What is the correct way of building logic to handle blank fields?

    Thanks for any help,
    Bob Chapman

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    There are a couple of basic ways. One is to use the IsNull() function in your query, and the other is to use the Nz() function.
    The IsNull() syntax looks like:
    SELECT Field1, IIf(IsNull(Field2),"Field2 is blank",Field2) as PseudoField2, Field3 FROM tblPersonnel
    The Nz() syntax is similar:
    SELECT Field1, Nz(Field2,23), Field3 FROM tblPersonnel
    The key is to use one of those functions depending on your specific needs. Check out the VBA Help for each of the functions to see examples and considerations.
    Wendell

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    120
    Thanks
    3
    Thanked 2 Times in 2 Posts
    Thanks, Wendell, that's a good help and solves my problem.

    Bob Chapman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •