468,167 Members | 1,893 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,167 developers. It's quick & easy.

Can someone explain this ISNULL sentence for me?...

Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan
Nov 21 '05 #1
3 1687
Hi,

SQL statement that replaces a null with the specified value.
http://msdn.microsoft.com/library/de...ia-iz_6mek.asp

Ken
------------------
"Jan Nielsen" <Re**************@tiscali.dk> wrote in message
news:ef**************@TK2MSFTNGP11.phx.gbl...
Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan

Nov 21 '05 #2
Hi Ken
Thanks for answering
Well at least it clarified one thing: isnull is a sql function. I looked in
the help for the vb functions.
It works very much like the nz function in vb.

But still...
If a datarow has a dbnull value I would expect the filter to become
something like this then:
mainDataSource.RowFilter = "Gender = 'male' and " 'Null Column' = 'Null
Column'"

Obviously this is incorrect as the column floor is not mentioned at all and
the last part will evaluate to true so gender = 'male' and TRUE.

I still do not get it

Jan

"Ken Tucker [MVP]" <vb***@bellsouth.net> skrev i en meddelelse
news:Ox**************@TK2MSFTNGP12.phx.gbl...
Hi,

SQL statement that replaces a null with the specified value.
http://msdn.microsoft.com/library/de...ia-iz_6mek.asp

Ken
------------------
"Jan Nielsen" <Re**************@tiscali.dk> wrote in message
news:ef**************@TK2MSFTNGP11.phx.gbl...
Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following
expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan

Nov 21 '05 #3
I'm back.
I kept thinking and now I think I understand it.
The filter will look at a row to see if gender is male. If so it will make
the isnull test for the floor column and if it is null it will return true
for this row (because 'null column' = 'null column' for this specific row)
which will then be included in the filter.

Is this about correct?

Jan

"Jan Nielsen" <Re**************@tiscali.dk> skrev i en meddelelse
news:%2********************@TK2MSFTNGP12.phx.gbl.. .
Hi Ken
Thanks for answering
Well at least it clarified one thing: isnull is a sql function. I looked
in the help for the vb functions.
It works very much like the nz function in vb.

But still...
If a datarow has a dbnull value I would expect the filter to become
something like this then:
mainDataSource.RowFilter = "Gender = 'male' and " 'Null Column' = 'Null
Column'"

Obviously this is incorrect as the column floor is not mentioned at all
and the last part will evaluate to true so gender = 'male' and TRUE.

I still do not get it

Jan

"Ken Tucker [MVP]" <vb***@bellsouth.net> skrev i en meddelelse
news:Ox**************@TK2MSFTNGP12.phx.gbl...
Hi,

SQL statement that replaces a null with the specified value.
http://msdn.microsoft.com/library/de...ia-iz_6mek.asp

Ken
------------------
"Jan Nielsen" <Re**************@tiscali.dk> wrote in message
news:ef**************@TK2MSFTNGP11.phx.gbl...
Hi
I am working with rowfilters in dataviews.
I would like to filter for empty fields (= null value in the database)
I found this sentence on msdn:
****************
To return only those columns with null values, use the following
expression:
"Isnull(Col1,'Null Column') = 'Null Column'"
*************************'
So I tried like this:
mainDataSource.RowFilter = "Gender = 'male' and " & "Isnull(floor,'Null
Column') = 'Null Column'"
And this actually works returning males that does not have a value in the
floor field.

But I do not understand why.
Isnull returns a boolean and does not take a column and a string. It
looks
more like the NZ function from visual basic.

Can someone explain this to me?

Best regards

Jan


Nov 21 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Trev | last post: by
8 posts views Thread by huhu | last post: by
8 posts views Thread by John | last post: by
4 posts views Thread by jimm.sander | last post: by
40 posts views Thread by aslamhenry | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.