Null or ""

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?
Derek BrownMDAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
I usually use this :

If IsNull (TextBox)  or Len(TextBox) = 0 Then
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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)?
0
Paul Cook-GilesAccess/VBA/SQL Database DeveloperCommented:
@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.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
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
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
0
Rey Obrero (Capricorn1)Commented:
like Anders I also use

If Me.ControlName & ""="" then
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
> 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 ...
0
Rey Obrero (Capricorn1)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.
0
Derek BrownMDAuthor 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.
0
Rey Obrero (Capricorn1)Commented:
<How can "Me.ControlName & "" actually equal "" >

if there is nothing in the .ControlName , concatenating a zero length string "" will result to ""
0
Derek BrownMDAuthor Commented:
Hi Ray
I get that but why not just ask if there is nothing in .ControlName Then
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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.
0
Rey Obrero (Capricorn1)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
0
Derek BrownMDAuthor 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
0
Dale FyeOwner, Developing Solutions LLCCommented:
"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.
0
Gustav BrockCIOCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
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
0
Derek BrownMDAuthor 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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
Rey Obrero (Capricorn1)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
0
Derek BrownMDAuthor 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
0
Dale FyeOwner, Developing Solutions LLCCommented:
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.
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.
0
Dale FyeOwner, Developing Solutions LLCCommented:
Good catch, Chrystal.

Actually char and nchar are fixed length strings, nchar can store unicode, char cannot.
varchar and nvarchar are variable length strings.
1
Derek BrownMDAuthor Commented:
Wow! Thank you both...........again

Derek
0
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
you're welcome, Derek ~ happy to help

... and thanks for the extra info, Dale ~ nvarchar seems like Access text with unicode
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.