Filtering Queries that use a fucntion to return a value (Null Values in a Date Datatype)

[Ken]

I wrote a function to use in queries that takes a date and adds or
subtracts a certain length time and then returns the new value. There
are times when my function needs to return Null values.

Function DateCalc (blah...) As Variant
Do Stuff...
If Not IsNull(varNewDate) Then
DateCalc = varNewDate
End If
End Function

Then in a query - MyDate: DateCalc([Blah], [Blah], [Blah])

The results of this are in MyDate are left justified dates with a few
blank rows where the function returns a null value.

I can't filter the results on "Not Is Null", so I changed things a
bit:

MyDate: Nz(DateCalc([Blah], [Blah], [Blah]), "")

And using the criterion <> "". This seems like it will work, (I can
see the rows get populated and there are no rows with blanks), but at
the end there is an error message: Date type mismatch in criterion
expression. Then all of the cells in the results are filled with
"#Name?".

I even tried CStr(Nz(DateCalc([Blah], [Blah], [Blah]), "")), but got
the same results.

So next I changed the datatype of the funtion to Date, specified that
all the rows that would have returnd Null return "08/08/1888" (not
optimal, I know), and then running the query.

MyDate: DateCalc([Blah], [Blah], [Blah])
<>#08/08/1888#

It also looked like it would run fine, but at the end I got the same
error message about a data type mismatch in the criterion expression.

Finally, I tried querying my first query and using criterion in the
second query instead of using a criterion in my user defined function
field, but that also gave the data type mismatch error.

I don't know if it matters, but the MDB files reside on a Samba server
network.

What principles about functions and using them in queries and dealing
with Null values do I need to understand to overcome this problem?
Any suggestions?

Thanks!

[MGFoster]

== Reply at bottom ==

Ken wrote:

I wrote a function to use in queries that takes a date and adds or
subtracts a certain length time and then returns the new value. There
are times when my function needs to return Null values.

Function DateCalc (blah...) As Variant
Do Stuff...
If Not IsNull(varNewDate) Then
DateCalc = varNewDate
End If
End Function

Then in a query - MyDate: DateCalc([Blah], [Blah], [Blah])

The results of this are in MyDate are left justified dates with a few
blank rows where the function returns a null value.

I can't filter the results on "Not Is Null", so I changed things a
bit:

MyDate: Nz(DateCalc([Blah], [Blah], [Blah]), "")

And using the criterion <> "". This seems like it will work, (I can
see the rows get populated and there are no rows with blanks), but at
the end there is an error message: Date type mismatch in criterion
expression. Then all of the cells in the results are filled with
"#Name?".

I even tried CStr(Nz(DateCalc([Blah], [Blah], [Blah]), "")), but got
the same results.

So next I changed the datatype of the funtion to Date, specified that
all the rows that would have returnd Null return "08/08/1888" (not
optimal, I know), and then running the query.

MyDate: DateCalc([Blah], [Blah], [Blah])
<>#08/08/1888#

It also looked like it would run fine, but at the end I got the same
error message about a data type mismatch in the criterion expression.

Finally, I tried querying my first query and using criterion in the
second query instead of using a criterion in my user defined function
field, but that also gave the data type mismatch error.

I don't know if it matters, but the MDB files reside on a Samba server
network.

What principles about functions and using them in queries and dealing
with Null values do I need to understand to overcome this problem?
Any suggestions?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I ran across a similar problem today (same error, but different set up).

I had one query that used DateAdd() function to increment a Date column
value by 30 days. Sometimes the date value was NULL. Running query 1 -
everything came out as expected: Null Date columns return null results
& valid dates were incremented correctly.

But, when I incorporated query 1 into query 2 - when a NULL Date value
was processed by the DateAdd() & passed to query 2 the type mismatch
error occurred.

I finally used DateColumn + 30 instead of DateAdd("d", 30, DateColumn).
The error disappeared & query 2 worked as expected.

I figured NULL was somehow discombobulating the DateAdd() function, and
I knew that a NULL + integer would result in NULL, which is what I
wanted passed to query 2.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQHSzoYechKqOuFEgEQJNOACdFubY0lhbwv4eD5LVNHnBGf +1T/cAnR1/
SiYxk6bEyGRD2XyzybrQoHWD
=lK/o
-----END PGP SIGNATURE-----