Null or ""

I usually use this :

If IsNull (TextBox)  or Len(TextBox) = 0 Then

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)?

@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.

If IsNull(Me!TextBox.Value) Then

Select allOpen 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

Select allOpen 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.

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.

like Anders I also use

If Me.ControlName & ""="" then

> 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 ...

<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.

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.

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

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

Hi Ray
I get that but why not just ask if there is nothing in .ControlName Then

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

Select allOpen in new window

Then I use it like:

IF IsNullOrBlank(me.ControlName) then

Select allOpen 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.

try this in the immediate window

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

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

"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.

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

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

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.

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

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

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.

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.

Good catch, Chrystal.

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

Wow! Thank you both...........again

Derek

you're welcome, Derek ~ happy to help

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