User Defined Function criteria/default problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AkAlan - 19 Apr 2006 18:28 GMT

I have a udf with a date column and I would like to be able to filter
returned records by passing NULL as a criteria. I set the column default
value to NULL. When I open the function and select either default or null
from the drop down I get no records returned. This in not a data problem, I
verified there are records with null values. I'm using MS Access2003 and SQL
Server 2000. Thanks for any help.

Reply to this Message

Brendan Reynolds - 19 Apr 2006 22:39 GMT

Without seeing the SQL, I can only take a wild guess. For whatever it may be
worth, my wild guess is that you may be attempting to compare the field with
Null using the = operator, something like so ...

SELECT dbo.TestTable.*
FROM dbo.TestTable
WHERE (TestField = @TestParam)

If so, you need to use 'IS NULL' to test for Null values, something like so
...

SELECT dbo.TestTable.*
FROM dbo.TestTable
WHERE (TestField= @TestParam) OR
(TestField IS NULL) AND (@TestParam IS NULL)

If that's not it, someone will probably be able to see what the problem is
if you post the SQL.

Signature

Brendan Reynolds
Access MVP

>I have a udf with a date column and I would like to be able to filter
> returned records by passing NULL as a criteria. I set the column default
[quoted text clipped - 4 lines]
> SQL
> Server 2000. Thanks for any help.

Reply to this Message

AkAlan - 20 Apr 2006 00:06 GMT

Here is the SQL for the functoin. I went to the properties of the function
and put NULL for the default. I open the function through the query window
and get prompted for MngrFinalReview, I only want those records where
Qc_Car_Mngr_Final_Review is null. I select NULL from the drop down list and
get no records returned.

SELECT Qc_Car_Insp_Num, Qc_Car_Mngr_Final_Review
FROM dbo.tblQc_Car
WHERE (Qc_Car_Mngr_Final_Review = @MngrFinalReview)

>Without seeing the SQL, I can only take a wild guess. For whatever it may be
>worth, my wild guess is that you may be attempting to compare the field with
[quoted text clipped - 20 lines]
>> SQL
>> Server 2000. Thanks for any help.

Reply to this Message

Brendan Reynolds - 20 Apr 2006 00:23 GMT

Well, there you go then - my guess was right, you're using '='.

The problem is that Null represents an unknown value, and therefore is never
equal to anything, including another Null value. Is one unknown value equal
to another known or unknown value? The answer is neither True nor False, but
unknown, i.e. Null. That is why you need to use IS NULL in SQL (or the
IsNull() function in VBA) to test for Null values.

SELECT Qc_Car_Insp_Num, Qc_Car_Mngr_Final_Review
FROM dbo.tblQc_Car
WHERE (Qc_Car_Mngr_Final_Review = @MngrFinalReview) OR
(Qc_Car_Mngr_Final_Review IS NULL) AND (@MngrFinalReview IS NULL)

Signature

Brendan Reynolds
Access MVP

> Here is the SQL for the functoin. I went to the properties of the function
> and put NULL for the default. I open the function through the query window
[quoted text clipped - 36 lines]
>>> SQL
>>> Server 2000. Thanks for any help.

Reply to this Message

aaron.kempf@gmail.com - 20 Apr 2006 16:32 GMT

so don't ever use a null anywhere; this is especially true for
dimenisons and facts if you're going to be using olap.

if you're not using olap-- you should be it rocks.

these mdb kids don't know what they're missing

Reply to this Message

AkAlan - 20 Apr 2006 17:47 GMT

I have just recently been tasked to migtate all mdb's to SQL and am learning
something new every day. I would like to understand more about never using
nulls, I just don't see how I can avoid them. If I have a date column that
has not been filled in and I would like to see all records which satisfy that
criteria, what other method would I use. The date column is either populated
with a date or null.

>so don't ever use a null anywhere; this is especially true for
>dimenisons and facts if you're going to be using olap.
>
>if you're not using olap-- you should be it rocks.
>
>these mdb kids don't know what they're missing

Reply to this Message

Baz - 24 Apr 2006 11:09 GMT

> I have just recently been tasked to migtate all mdb's to SQL and am learning
> something new every day. I would like to understand more about never using
> nulls, I just don't see how I can avoid them. If I have a date column that
> has not been filled in and I would like to see all records which satisfy that
> criteria, what other method would I use. The date column is either populated
> with a date or null.

Ignore him, he only comes here to make a lot of noise and draw attention to
himself.

Reply to this Message

aaron.kempf@gmail.com - 25 Apr 2006 20:30 GMT

baz

if you're talking about me then eat sh.t ok?

Reply to this Message

AkAlan - 20 Apr 2006 17:53 GMT

Thanks Brendon! This worked perfectly and I now understand the concept of
null much better.

>Well, there you go then - my guess was right, you're using '='.
>
[quoted text clipped - 14 lines]
>>>> SQL
>>>> Server 2000. Thanks for any help.

Reply to this Message