Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Blank' Field Not Blank (Access 2000)

    I tried to use some expressions to check the string length of some fields. Some fields that appear to be blank turn out not to be blank when the expression applies. For example, if I use "IIF([name]="",... " or "IIF(LEN([name]=0, ... " . In both cases, what appears to be blnak fields don't work. Why ? Help !

  2. #2
    4 Star Lounger
    Join Date
    Aug 2003
    Location
    Stroud, United Kingdom
    Posts
    548
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Blank' Field Not Blank (Access 2000)

    You could try using IIf(Isnull([name]),....

    or IsEmpty?
    Thanks,

    pmatz

  3. #3
    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 Not Blank (Access 2000)

    You may be having a problem with null fields. When [name] is null, then the answer to Len([name]) is also null which does not equal zero.

    Try Len(Nz([name],"")) which will evaluate to 0 when you have a name that is null. This will also work with your OR clause.

    HTH

    Immediately edited to clarify null field.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Lounger
    Join Date
    Jan 2003
    Location
    Herndon, Virginia, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Blank' Field Not Blank (Access 2000)

    There's a couple of possibilities that I can think of. One is that the field is Null, not an empty string (you could use the IsNull() function to check for that. Another is that the field does actually have some spaces or blank characters in it. You could put the trim() function around the field to remove any extra spaces. e.g. "IIF(trim([name])="",... " or "IIF(LEN(trim([name])=0, ... "

Posting Permissions

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