Access: checking blank variables

Wednesday, September 9, 2009

Microsoft Access I often have to test String, Variant or Object variables that have no content and could be considered ‘blank’.

The problem is that testing for “blankness” can mean many different things to different types:

  • For an Object type, the variable can be Nothing.
  • For a String type, the string can have no content at all: "", vbNullString.
  • For a Variant type, the string can have any of the following attributes or values:
    • it can be Missing if the variable is an unused optional parameter,
    • it can be Empty if it was never assigned,
    • it can be Null if, for instance it’s bound to a nullable field or unbound with no value,
    • it can be an empty string "", vbNullString.

When having to check these variables in code, it can be tiresome to have to go through testing some of these possibilities just to find out that your variable does or not not contains something useful, regardless of the type of variable you are using.

To avoid having to do all these tests, make the code a bit more tidy and allow me to move on to more important things, I use this small utility function quite often:

'-----------------------------------------------------------------------------
' True if the argument is Nothing, Null, Empty, Missing or an empty string .
'-----------------------------------------------------------------------------
Public Function IsBlank(arg As Variant) As Boolean
    Select Case VarType(arg)
        Case vbEmpty
            IsBlank = True
        Case vbNull
            IsBlank = True
        Case vbString
            IsBlank = (arg = vbNullString)
        Case vbObject
            IsBlank = (arg Is Nothing)
        Case Else
            IsBlank = IsMissing(arg)
    End Select
End Function

So now I don’t have to worry so much about the type of the variable I’m testing when I want to know if it contains useful data:

...
' Here assume that CustomerReference is a control on a form.
' By using IsBlank() we avoid having to test both for Null and empty string.
If IsBlank(CustomerReference) Then
   MsgBox "Customer Reference cannot be left blank."
End If
...

Obviously, IsBlank() doesn’t replace the other tests but I found it to be more straightforward to use in most cases.

Entry Filed under  :  Database,MSAccess,Programming

2 Comments Add your own

  • 1. Patrick Honorez  |  February 17th, 2010 at 2:49 pm

    For strings, it is apparently faster to test if LEN(strTest) > 0, since VBA stores strings starting with bytes containing the length of the string.

  • 2. James Barker  |  April 25th, 2013 at 11:18 pm

    I have always wanted to know this

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links