Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Blank field in criteria (2000, 2002)

    I want to run a query with criteria for a Middle Name field. Some records are blank, of course. I would like to find all the blank middle names, but I can't. Is Null in the criteria returns no records, ditto (" ") , without the space. The Table entry for the field property Allow Zero Length is NO. What am I doing wrong?

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Blank field in criteria (2000, 2002)

    Howard,

    If Is null and " " or "" in the criteria is not returning data then something must be there.

    Try Len([MiddleName]) in the query to see if something is there. This will return the length of the data in the middle name field.

    Another option is use the trim feature on the field, i.e., Trim([MiddleName]) to get rid of any trailing spaces and retry the is null or "". Is this field a text field or other type?

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Brookline, Massachusetts, USA
    Posts
    606
    Thanks
    17
    Thanked 0 Times in 0 Posts

    Re: Blank field in criteria (2000, 2002)

    Gary, thanks for the reply. I added a calculated field for LEN next to middle name. It returned many records with no length (field was blank). I then used ISNULL in the criteria of the calculated field, and found all the blank middle name records. Bingo! The count agrees with an alpha sort of the Table's middle name field, where all the blanks sort first.
    The middle name field is text, so I don't understand why Is Null didn't work in the first place. Some of the records never had any data in middle name, others may have had a character accidently added, then deleted. But I got zero records returned in my first try. Very odd.

Posting Permissions

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