Results 1 to 3 of 3
  1. #1
    Paul H's Avatar
    Paul H is offline Expert
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Richmond, VA

    Empty String Function

    I have on ongoing struggle with empty strings vs. null. I've learned a bit over time, but they look the same and of course behave differently. I have written a function to check for both a null and an empty string, "". and then return a substitute value like the NZ function. I'm not sure if it's necessary, but it solves a problem I was having with an empty combo box on my form. NZ was not working so I wrote this.

    Public Function NZ_Empty(varString As Variant, strSubstitute As String)
        If IsNull(varString) Or varString = "" Then
            NZ_Empty = strSubstitute
            NZ_Empty = varString
        End If
    End Function
    I've seen other developers do similar things. This function works in place of NZ. Does Access have something built in like this? I'd appreciate your comments.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    The description and syntax of Nz() and IsNull() are at these links

    Lots of posters seem to use blank, empty and Null interchangeably and as you have said
    they behave differently.

    You may find this article by Susan Harkins interesting

  3. #3
    Missinglinq's Avatar
    Missinglinq is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Richmond (Virginia, not North Yorkshire!)
    Quote Originally Posted by Paul H View Post
    Does Access have something built in like this?
    If you're asking if there is a native (built-in) Function that checks for Nulls and Zero-Length Strings ("")...the answer is no! You have to roll your own, as you've done. There are three or four hacks for doing this, and everyone seems to have one in particular that they like.

    Linq ;0)>
    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-27-2015, 08:26 AM
  2. function to extract numbers from string
    By Ruegen in forum Programming
    Replies: 4
    Last Post: 04-12-2015, 07:59 PM
  3. Replies: 6
    Last Post: 01-21-2014, 06:39 PM
  4. Replies: 3
    Last Post: 08-22-2013, 12:14 PM
  5. NULL vs. Empty String
    By obiron in forum Access
    Replies: 3
    Last Post: 08-02-2011, 08:43 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Other Forums: Microsoft Office Forums - Senior Forums