Handling NULL values in form 3

[dbero]

When I click a command button, I am going out to various textboxes, option Groups, etc. and pulling the data together. However, as an example, the code below will RunTime error 94 because of invalid use of Null when their is no text entered into text24. If there is no text entered, I'd like the code to recognize that and do something else.

DriversLic = Text24.Value

Thank you!

 

[ProgramError]

if isnull(me.text24) then
do something
else
do something else
end if


Ian Mayor (UK)
Program Error
Programmers do it one finger at a time!

 

[cascot]

dbero,

It depends on what type of data you are expecting from each textbox, for example if you are expecting text you may wish to treat a Null as an empty string (""), or in the case of numeric data you may want to record a null as zero, it will all depend on what your needs are.

Anyway, the code you are looking for will be along the following lines...

If IsNull([Text24]) then
  DriversLic = ""
Else
  DriversLic = [Text24]
End If

You may want to consider renaming textboxes to something more relavant to the data they contain as object names like [Text24] leads to code that is hard to follow.

 

[jsteph]

I think a good catch-all way to catch either NULL or "" (empty string)--assuming you don't differentiate between empty string and null--is this:

If Trim("" & [Text24]) = "" then
  DriversLic = ""
Else
  DriversLic = [Text24]
End If

Concatenating an empty space with anything in Trim() will return a string. This reduces the clutter of things like:
If IsNull([Text24]) or [text24] = "" then...
--Jim

 

[cascot]

jsteph,

I'm not quite sure where the "clutter" is in If IsNull([Text24]) but the IsNull approach is certainly more efficient. Yes the following is more of a theoretical excercise than a practical one but I put your solution in a loop of 100,000 iterations and it took 122 millieseconds. The IsNull approach took 35 millieseconds.

Think of what you could do with that 87 millieseconds ;-)

 

[dbero]

Thank you very much. I have also discovered that declaring the variable as a Variant will eliminate the run time error because variants can better handle null/empty strings.

 

[cascot]

dbero,

In general it is better to avoid the use of Variants and instead handle the exceptions.

 

[PHV]

Only variants may hold Null value ...

 

[cascot]

PHV,

Absolutely true. Like I said "in general". The majority of the time you should be able to avoid scenarios where you actually need to assign a Null value.

 

[PHV]

In the databases world it's quite common to have columns allowing Null (for unknown value).

 

[jsteph]

cascot,
My solution was not intended to boost performance, I don't really fret over a few nanoseconds per iteration.

It's to reduce clutter--the clutter is not in just IsNull(), but in using both:

If IsNull(SomeField) Or SomeField = "" then...

I've seen that many, many times.
--Jim

 

[cascot]

There's also the Nz function which can be used to test a variable of type variant and return a specific value if it's value is null.

For example...

DiversLic = Nz([Text24],"")

Which will either assign the value of Text24 or "" if Text24 is Null.

 

[cascot]

PHV,

I thought that in such cases simply not assigning a value was the equivalent of Null and thus you didn't actually have to assign a Null to the field, it just didn't have a value set at all thus leaving it as Null. But maybe that is incorrect.

 

[PHV]

simply not assigning a value was the equivalent of Null
Yes, provided that DEFAULT is not set (or set to NULL).