Access: checking blank variables
Wednesday, September 9, 2009
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
Objecttype, the variable can beNothing. - For a
Stringtype, the string can have no content at all:"",vbNullString. - For a
Varianttype, the string can have any of the following attributes or values:- it can be
Missingif the variable is an unused optional parameter, - it can be
Emptyif it was never assigned, - it can be
Nullif, for instance it’s bound to a nullable field or unbound with no value, - it can be an empty string
"",vbNullString.
- it can be
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 = (LenB(arg) = 0)
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
This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
6 Comments
1. Patrick Honorez | February 17th, 2010 at 14:49
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 23:18
I have always wanted to know this
3. Pavlo | December 25th, 2014 at 02:25
Just wondering in what case IsMissing could be used? Is is applicable only to optional arguments.
4. Renaud Bompuis | December 25th, 2014 at 08:59
@Pavlo: yes,
IsMissingis only used for optional variant parameters in a function or sub that were not supplied to the caller. It’s only used in that case.5. Pavlo | December 25th, 2014 at 09:02
My point was that you do not have Optional arguments in IsBlank function. Thus line: IsBlank = IsMissing(arg) will never be executed?
6. Renaud Bompuis | December 26th, 2014 at 10:34
@Pavlo: it will be executer. the information whether a particular variant is in the
Missingstate is part of the variant structure itself and so it’s passed on as you pass on the variant to other subs and functions.A variant is only initialised in the
Missingstate when it’s created to replace an optional argument that was not passed by the caller.To illustrate this:
Public Sub Test(Optional optArg As Variant) CheckMissing optArg End Sub Public Sub CheckMissing(arg As Variant) Debug.Print IsMissing(arg) End SubThis will print
True, even though theargargument is not missing.