We help IT Professionals succeed at work.

Null or ""

Derek Brown
Derek Brown asked
on
125 Views
Last Modified: 2017-04-09
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

Paul Cook-GilesSenior Application Developer
CERTIFIED EXPERT

Commented:
I usually use this :

If IsNull (TextBox)  or Len(TextBox) = 0 Then
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
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
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
Top Expert 2016

Commented:
like Anders I also use

If Me.ControlName & ""="" then
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
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 ...
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.
Anders Ebro (Microsoft MVP)Microsoft Developer
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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