User Defined Function criteria/default problem

[AkAlan via AccessMonster.com]

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.

 

[Brendan Reynolds]

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.

 

[AkAlan via AccessMonster.com]

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

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.

 

[Brendan Reynolds]

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)

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

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.

 

[aaron.kempf]

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

 

[AkAlan via AccessMonster.com]

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.

 

[AkAlan via AccessMonster.com]

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

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)

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]

 

[Baz]

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.

 

[aaron.kempf]

baz

if you're talking about me then eat shit ok?