Null or ""

Derek Brown
Derek Brown used Ask the Experts™
on
When creating a procedure based on no value in a text box should I use

If IsNull (TextBox)  = True Then

or

If TextBox = "" Then

Does the same apply to a similar procedure for a control with a number format?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Paul Cook-GilesSenior Application Developer

Commented:
I usually use this :

If IsNull (TextBox)  or Len(TextBox) = 0 Then
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
it is important to realize that Null is not equal to "". Null is NO value. "" is a value ... does the control have a default value? Do you 'clear' the control by setting it to "" (which is not the same as setting to Null)?
Paul Cook-GilesSenior Application Developer

Commented:
@Crystal, that's certainly true... but as I use the CodeTemplate functionality in M-ZTools* to create the If--Else--EndIf structure, I want something that will handle either numeric or alpha-numeric data types.  :)

*I can't recommend https://www.mztools.com/ highly enough.  It looks like their free VBA add-in is no longer available, so I'm looking at their purchase-based tool.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
If IsNull(Me!TextBox.Value) Then

Open in new window


will always work as you probably never will allow empty strings - and should you do, you will know that.

/gustav
Since one could come across zero length strings (e.g. a string variable cannot be null. It is initialized to an empty string).
So, as such, I've used the approach
If Me.ControlName & ""="" then

Open in new window


This will work for variables, comboboxes, and textboxes, regardless of whether its an emptry string or Null. This keeps it simple in my book.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
thanks, Paul. MZ Tools is certainly a great product. For years it was free -- now it is about 60 bucks, which isn't much for all the time that has gone into building it.  The author, Carlos Quintero, is a long-time Microsoft MVP (Visual Studio).  The MZ-Tools interface has changed so it would be good to read his tutorials and help.

I agree, however, with gustav, and that is also the method I use myself because when a control is cleared, it is set to Null.  If data containing zls ("") is imported, it gets changed

... if, however, you are connecting to a Sql Server or other table that uses "" for an empty string instead of Null for no value, then you would want to test for that -- and use "" in the control instead of Null too.  I'm sure Carlos was just covering all the cases in MZ tools to make it more generic, but if you know your data, then you also know which case to use.
Top Expert 2016

Commented:
like Anders I also use

If Me.ControlName & ""="" then
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
> Me.ControlName & ""=""

if the control is bound to a numeric or date data type, wouldn't this result in implicit conversion to string? It would be interesting to know which has better performance for a number/date between this and testing IsNull ...
Top Expert 2016

Commented:
<if the control is bound to a numeric or date data type, wouldn't this result in implicit conversion to string? >
does it matter?
we are just testing if the control has value or not.

Author

Commented:
Well I can't believe that the answer to that question wasn't simpler. It's amazing how experts differ on many aspects of VBA. So thanks to you all!

Paul/Crystal
I don't set controls to ""  and I'm interested in MZ tools. I will look it up

Gustav,
I am surprised at "If IsNull(Me!TextBox.Value) Then" If the control is named and the code is in the underlying form what is the purpose of the explicit "Me!" and why ".Value"  But I also notice that it does not appear that "= True" is required either, fascinating.

Anders/Ray
Another fascinating one. How can "Me.ControlName & "" actually equal "" totally illogical to my simple brain. Make no mistake I am sure it works but why eludes me.
Top Expert 2016

Commented:
<How can "Me.ControlName & "" actually equal "" >

if there is nothing in the .ControlName , concatenating a zero length string "" will result to ""

Author

Commented:
Hi Ray
I get that but why not just ask if there is nothing in .ControlName Then
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, I wrote a simple function that does both tests, and use it:

Public Function IsNullOfBlank(SomeValue as Variant) as Boolean

    IsNullOrBlank = (Trim(SomeValue & "") = "")

End Function

Open in new window

Then I use it like:
IF IsNullOrBlank(me.ControlName) then

Open in new window

Which makes it very clear to those that follow behind me what I'm checking for.
Regarding:
<How can "Me.ControlName & "" actually equal "" >

If the control is Null, then Null & "" is equal to "". Its just how it is defined. Whereas Null + "" is Null. This can be usefull in certain cases.
If the control has a zero length string, then think of it 0+0=0
If the control is anything besides null or zero length string "", then something is in the control.

@Crystal: You are correct. It would result in an implicit conversion to a string. However, performance in this case is irrelevant. You could do that conversion 100.000 times and not even notice the time taken.
Top Expert 2016

Commented:
try this in the immediate window

?Null & ""=""    'Null
True
?"" & ""=""        'zls
True
?1 & ""=""         'number
False
?#1/1/2017# & ""=""   'date
False
?"A" & ""=""      'Text or string
False

Author

Commented:
Well thank you all for that!

Reading all it seems the only time that:
If IsNull (Me.TextBox)  = True Then would fail is if there is a zls  present, correct?

I would still like to know why the "Me." is needed. In this instance what is it's purpose

How would you place a zls in a field
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
"Me" in me.TextBox refers to the object housing the textbox, in this case a form.  It is a more explicit reference to the TextBox control.

No, the only time IsNull(me.TextBox) will return a NULL is if the field that TextBox is bound to is NULL.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
To repeat myself:

   If IsNull(Me!TextBox.Value) Then

will always work as you probably never will allow empty (zero length) strings - and should you do, you will know that.

You may often get away with:

    If IsNull(TextBox) Then

but that coding style is not good, as it my indicate, that TextBox is a the name of a variable. You may avoid that with the old syntax from Access Basic (Access 1.x and 2.0):

    If IsNull([TextBox]) Then

which will work for a control name but fail for a variable name. With Me! as prefix, you leave no doubt, that you are referencing a control of the current form:

    If IsNull(Me!TextBox) Then

Specifying the property Value is not needed for form controls as it is the default property, but it often is for Field objects:

    If IsNull(rs!SomeField.Value) Then

but is doesn't hurt and leaves no doubt what your intensions are, and is widely used in VB.NET and C#, so you see it more and more in VBA as well.

The name of the function IsNull() clearly indicates that is returns True or False which it does, thus it is superfluous to check once again if it is True:

    If IsNull(Me!TextBox.Value) = True Then

This sums up to:

    If IsNull(Me!TextBox.Value) Then

The few cases where you may meet zero length strings are when you positively specify it yourself for some reason (which has to be good), or if you connect to a database out of your control, where it has been specified. For those cases, the simple (and fastest) check is:

    If Nz(Me!TextBox.Value) = "" Then

because Nz by default replaces Null with an empty string. Thus this expression will return True for both Null and a zero length string.

/gustav
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
the only time IsNull(me.TextBox) will return a NULL is if the field that TextBox is bound to is NULL.

It will always return True for an empty textbox, also if unbound.

/gustav

Author

Commented:
That really is comprehensive Gustav.

So to sum up:
If I can guarantee that there are no zero length strings in "TextBox" control and guarantee that there is no variables with "TextBox" name then this would work every time:

If IsNull(TextBox) then
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
The key word in your last statement is "guarantee"; there are no guarantees!

I cannot tell you have many Access databases I've taken over where the developer allowed zero length strings in the table designs, or where a SQL Server developer inadvertently used varchar(10) instead of nvarchar(10) to define a field.  In case you are not aware, varchar(10) will return 10 spaces if the field contains no data.

These are the reasons that I created my little "IsNullOrBlank( )" function.  It is very clear what I'm testing for, and will identify fields where the value passed (could be a control value, field value, or variable value) is either NULL, a ZLS, or contains nothing but spaces.

Just my 2 cents.
Top Expert 2016

Commented:
this is guaranteed to work everytime  and is the easiest and simplest way

If Me.ControlName & ""="" then

it can also be use to test for strVariables

if strVariables & ""=""  then

Author

Commented:
Thanks Dale.

I always use native access features. I have never defined a field with code. I admit this is because of ignorance really but at the same time I find my applications run absolutely rock solid and literally never crash despite having around 30,000 lines of code.

I don't and can't do the clever stuff that you guys do. So I love the really simple stuff.

I would still like to know how apart from your comment above how and why would you want zero length string in a field

Thanks again Dale
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Well, a NULL, by definition means that you don't know what the value is, could be anything.  A zero length string, means, nothing?  

So, if the [Name_Middle] in your table is NULL, it means you don't know what it is, but if it is a ZLS then it would imply that you don't have one.  Many developers will require a user to enter N/A or nmn to reflect this "value", but in either case, the middle name actually doesn't exist.
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
dot (.) vs bang (!)

Intellisense only works with the dot (property or method) not bang (object member) so, imo, it is convenient to use Me. instead of Me! ... and the compiler won't check names for bang.

why might you want to use ! instead of . ?

On occasion, when the record source may not be available when developing, or it may be different depending on conditions, Me! won't result in an error when compiling (it will error when you run if there is an issue) ... but neither will you have the benefit of checking names during compilation.

if there is no control by the name specified at runtime then Access will look in the recordset for a field

~~~
good point about data types, Dale -- and thanks for clarifying varchar vs nvarchar (is that backwards?).  If, however, structure is changeable, especially if it is in Access. IMO, it is good to convert anything imported that is 'null' to actually be Null, and then test IsNull. Perhaps also remove the default value of 0 (zero) that Access automatically 'helpfully' supplies when a numeric data type is specified -- at least give it consideration.

I can see how a generic function is helpful to work with data when you take over a database to make development go faster ... but if you know your data and have control over it, a preferable approach is to ensure good data structure and good rules. Not everyone wants to go this far, or has the time.  

> "why would you want zero length string in a field"

depending on how structure is defined, there may not be capability to store 'no value' so zls steps in -- or, as Dale pointed out, it might be a bunch of spaces not empty string ("", zls=zero-length-string). At times, it does make sense to store trailing spaces but it can also be a pain as well as waste of space.

> "A zero length string, means, nothing?  "

zls is a value, Null is no value. Since Access can 'store' Null, imo, it is a good idea when the data is unknown. "" might be used if the field was evaluated and actually has no value (like Dale's middle name comment) -- and Access can tell the difference -- much like 0 might be entered for a number if that is really the value.

Another consideration is whether the field is part of a (probably natural) primary (and maybe foreign) key since then it must have a value to match.  Access will not match Null with Null.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Good catch, Chrystal.

Actually char and nchar are fixed length strings, nchar can store unicode, char cannot.
varchar and nvarchar are variable length strings.

Author

Commented:
Wow! Thank you both...........again

Derek
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015

Commented:
you're welcome, Derek ~ happy to help

... and thanks for the extra info, Dale ~ nvarchar seems like Access text with unicode

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial