Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Isnull, IsEmpty, is '' (Access 2007)

    In a control on a form , the user is supposed to enter a text value.
    By mistake It is possible that the user enter a text in the control and directly after that delete the entered value without putting another text value in it.
    So we can have the situation that nothing is entered in the control, or something entered and after that deleted.
    I such a case how do we check the control for the presence of a text value:
    Do we use:
    IsNull(me!txtArtnr) ,
    or
    IsEmpty(me!txttArtnr)
    or
    if me!txtArtnr = ""
    or
    If Isnull(me!txtArtnr) or IsEmpty(me!txtArtnr) or me!txtArtnr = ""

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    I'd set the Allow Zero Length property ("Lengte nul toestaan" in Dutch) of the field in the table to No, and the Required property to Yes. That way you don't need code.

    If you prefer code, you can use
    <code>
    If Me.txtArtNr & "" = "" Then
    </code>
    This catches both Null and "". There's no point in using IsEmpty here.

  4. #3
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Brugge, Belgium
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    Thank you very much and what a suprise ... a response in the Dutch language !!!!

  5. #4
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    Hans, I like your piece of code; can you explain how it works, please.

  6. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    Both when txtArtNr is really blank / empty / null and when it contains a zero-length string, concatenating it with a zero-length string <code>""</code> results in a zero-length string. So the condition
    <code>
    Me.txtArtNr & "" = ""
    </code>
    is true whenever txtArtNr is blank or a zero-length string. Another possibility would have been
    <code>
    If Nz(Me.txtArtNr, "") = "" Then
    </code>
    The Nz function returns its first argument (here Me.txtArtNr) unless that is null, then it returns the second argument (here a zero-length string).

  7. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    Are you saying that "" = "" results in a zero-length string?
    If I do
    ? "" = "" in the immediate window it returns True. Similarly, if I do "" <> "" it returns False. I've tried things such as ""a= "" and it still returns True.

    This is the part that I can't quite understand.

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    No.

    1) If Me.txtArtNr is Null (blank, empty), <code>Me.txtArtNr & ""</code> results in <code>Null & ""</code> and that results in <code>""</code>. So the comparison <code>Me.txtArtNr & "" = ""</code> is true.
    2) If Me.txtArtNr is a zero-length string, <code>Me.txtArtNr & ""</code> results in <code>"" & ""</code> and that results in <code>""</code>. So the comparison <code>Me.txtArtNr & "" = ""</code> is true.
    3) If Me.txtArtNr is neither Null (blank, empty) nor a zero-length string, <code>Me.txtArtNr & ""</code> results in a non-empty string. So the comparison <code>Me.txtArtNr & "" = ""</code> is false.

  9. #8
    2 Star Lounger
    Join Date
    Sep 2002
    Location
    Chicago, Illinois, USA
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isnull, IsEmpty, is '' (Access 2007)

    You're right. I was tripping myself up on the quote strings. I was viewing the leftmost " and the rightmost " as enclosing an expression of " = ", which I now see is incorrect. "" = "" is an equality comparison of two zero-length expressions.
    Thanks for your patience.

Posting Permissions

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