Access: checking blank variables
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 beNothing
. - 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
.
- 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.
6 comments September 9th, 2009