Checking Date for Null

[oldblindpew]

I'm passing a date from a form control to a public function. What is good
technique for having the function check the date for Null? Simple example:

Calling statement (from Control Source) = MyFunction([txbDate])

Function MyFunction(dateDate as Date) As String
If IsNull(dateDate) Then
MyFunction = "something"
Else
MyFunction = "something else"
End If
End Function

This won't work, since a date variable cannot be Null. I've got it working
by declaring the date variable as Variant, but can this really be the right
answer?

Thanks,
OldBlindPew

 

[John W. Vinson]

This won't work, since a date variable cannot be Null. I've got it working
by declaring the date variable as Variant, but can this really be the right
answer?

If you need to allow for passing a NULL, yes, it's the right answer.

 

[Daniel Pineault]

You could try something more like:

Function MyFunction(dateDate As Variant) As String
If IsDate(dateDate) Then
MyFunction = "something"
Else
MyFunction = "something else"
End If
End Function

--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.

 

[Daryl S]

oldblindpew -

You can keep the variables as dates, and make the parameter optional. If
you set a 'default' date in the function statement, then you can test for it
instead of testing for a null. The function statement would be something
like this:

Function MyFunction(Optional dateDate as Date = #1/1/1950#) As String

 

[David W. Fenton]

Function MyFunction(Optional dateDate as Date = #1/1/1950#) As
String

Why not something much more unlikely as your default value, like
nothing at all (which means 0) or 1/1/100.

 

[oldblindpew]

I don't quite follow this suggestion. My form is for a document log. The
null date is an acceptable condition meaning either that the document has has
not yet been received, or that if received, the date is missing. My
Function's job is to interpret the data (or absence thereof), not tamper with
it.

I had thought that since a date variable cannot be null, the value passed
into the function by Access would have to be some sort of zero date, which I
could test for. Instead, John Vinson confirms this situation is normally
handled by declaring the variable as a Variant type, which is apparently
capable of holding a null date.

Thanks,
Pew

 

[David W. Fenton]

I had thought that since a date variable cannot be null, the value
passed into the function by Access would have to be some sort of
zero date, which I could test for. Instead, John Vinson confirms
this situation is normally handled by declaring the variable as a
Variant type, which is apparently capable of holding a null date.

DarylS was suggesting how to handle stronger data typing, i.e.,
using an optional argument and not passing it when the date was
Null. But optional arguments have to have default values, and you
can either test for the default value or IsMissing() to skip using
that variable.

Basically, for functions I'm going to call in the rows of a SQL
statement when the function needs data from fields that can be Null,
I'll declare the parameter as variant type. For functions I use in
code (and not in rows of a query), I'll use an optional parameter,
instead, and test for default value or IsMissing (depending on the
circumstances).

 

[Daryl S]

David -

I remember there being different values for a date = 0 depending on either
the version of Access or maybe of the OS, so I tend to pick an out-of-range
date, but the zero would probably work in this case as we are not storing it
to be read by different computers.

 

[David W. Fenton]

I remember there being different values for a date = 0 depending
on either the version of Access or maybe of the OS, so I tend to
pick an out-of-range date, but the zero would probably work in
this case as we are not storing it to be read by different
computers.

Within Access VBA, Date = 0 always means the same thing. If you're
mixing in with Excel, then you get variation, but within Access
itself, the value is 100% reliable. If it weren't, no data value
would be reliable, regardless of how you were using it (i.e., if in
some context 0 is 12/30/1899 in some cases and 12/31/1899 in others
it means that the integer part of 2/21/2010 is going to be different
in those two contexts, too, since it's the number of days since date
value 0.

 

[oldblindpew]

I tried this and it did not work:

Function MyFunction(dateDate as Date) As String
Debug.Print "dateDate = " & dateDate
If dateDate = 0 Then
MyFunction = "something"
Else
MyFunction = "something else"
End If
End Function

Even Debug.Print didn't work when the date is blank.

Puzzled As Usual,
Pew

 

[John W. Vinson]

I tried this and it did not work:

Function MyFunction(dateDate as Date) As String
Debug.Print "dateDate = " & dateDate
If dateDate = 0 Then
MyFunction = "something"
Else
MyFunction = "something else"
End If
End Function

Even Debug.Print didn't work when the date is blank.

You're dimming dateDate as Date.

Blank is not a valid value for a Date datatype. Neither is NULL.

Reread the thread... you must, MUST, use a Variant datatype if you want to
have a NULL accepted.

 

[David W. Fenton]

You're dimming dateDate as Date.

Blank is not a valid value for a Date datatype. Neither is NULL.

Reread the thread... you must, MUST, use a Variant datatype if you
want to have a NULL accepted.

He's replying to my discussion of using a date data type, but he's
ignored the part about not being able to pass a Null (i.e., you have
to have code to check before calling the function, or you have to
process the value to convert the Null into something else).

 

[oldblindpew]

Thanks guys, but please read the thread.

From my Original Post:
"This won't work, since a DATE VARIABLE CANNOT BE NULL."
and:
"I've GOT IT WORKING by declaring the date variable as VARIANT..."

From my second posting:
"I had thought that since a DATE VARIABLE CANNOT BE NULL, the value passed
into the function by Access would have to be some sort of zero date, which I
could test for. Instead, John Vinson confirms this situation is normally
handled by DECLARING THE VARIABLE AS A VARIANT type, which is apparently
capable of holding a null date."

I marked the John Vinson reply as having ANSWERED MY QUESTION. This would
have been the end of the matter for me, except Daryl and David continued the
discussion along the lines of some sort of default date to be supplied by the
programmer to make up for the fact that no provision has been made for a null
date. Later in the thread I thought they were saying there actually IS a
zero date, so I tried (again) to test for it, and used Debug.Print to
actually look at it, but with total lack of success.

I do not understand how or why a null date passed to a date variable results
in something that is neither null nor zero nor anything that Debug.Print can
print, but since it can be worked around by using a Variant type, and since
John Vinson says that this is how it should be done, I would say that marks
the end of the hunt.

Thanks again,
Pew

 

[Stuart McCall]

Hi Pew

FWIW my preference is to use defensive programming when dealing with date
parameters, ie ensure that a Null is never passed to the procedure. Just one
more way..

 

[David W. Fenton]

Daryl and David continued the
discussion along the lines of some sort of default date to be
supplied by the programmer to make up for the fact that no
provision has been made for a null date. Later in the thread I
thought they were saying there actually IS a zero date, so I tried
(again) to test for it, and used Debug.Print to actually look at
it, but with total lack of success.

There *is* a date value of 0, and it represents 12/30/1899.

But it's not at all the same thing as Null.

An unitialized date variable has a value of 0, but that's
problematic if you are using dates that overlap that range. If
you're not, testing for 0 can be a way of knowing that the value has
not been assigned.

However, as I said in one of my responses, I would use a variant
data type for a function parameter if I were using it in a query or
recordsource that was processing data where the date fields could be
Null.

But for subs/functions *not* used in SQL statements, I would never
use a variant data type, because I wouldnt' want to pass Nulls --
I'd want the protection of the date data type automatically
rejecting a passed Null value as an error.

In other words, it depends on the context.

I do not understand how or why a null date passed to a date
variable results in something that is neither null nor zero nor
anything that Debug.Print can print,

There is no such thing as a "Null date". There are only Nulls and
dates. A Null is an unknown and while text fields in data tables and
variant variables can store a Null to represent an unknown value,
the date variable cannot (a date field in a data table *can* store a
Null, but it's an UNKNOWN).

What you get from Debug.Print depends on what you passed it. Some
functions when passed a Null will return a Null (i.e., when you pass
them an unknown, they return an unknown). Others respond
differently. You need to examine the data types accepted by a
function's parameters and its return type to know what to expect
from the function when it is passed a Null. If its date parameter is
typed as a variant, it will be able to accept a Null, and if it's
return type is typed as variant, it will be able ot return a Null.
If the data types are something else, Nulls will neither be accepted
or returned by the function. This is by design, and it's a good
thing.

Whether or not you use date data type instead of variant depends
entirely on the purpose of your function and how/where it is used.

but since it can be worked around by using a Variant type, and
since John Vinson says that this is how it should be done, I would
say that marks the end of the hunt.

It entirely depends on context, and that's why I felt it was
important to provide the details about the "road not taken" in your
particular scenario.