IsNull() Alternative

IsNull() Alternative

Post by Steve Park » Mon, 09 Apr 2001 06:00:25



Hey...

This is probably a simple question.  If I am looping through a recordset and
encounter a null value, I want to skip it, but I can't do that.  Here are
the things I've tried:

if IsNull(rs.fields(0)) then
    'Do code block
end if

if rs.fields(0) = "" then
    'Do code block
end if

if rs.fields(0) = chr(0) then
    'Do code block
end if

Any other ideas?

Thanks.
-Steve

 
 
 

IsNull() Alternative

Post by Gary Huntres » Mon, 09 Apr 2001 08:26:30


Sometimes I find it easier to shut off error handling when I'm in a loop
like that:

on error resume next
do while rs.eof = false
    ' do block of code that may fail
    if err>0 then exit do
    rs.movenext
loop
on error goto 0

--
Regards,
Gary "SuperID" Huntress
=======================================================
FreeSQL.org offering free database hosting to developers
Visit http://www.freesql.org


Quote:> Hey...

> This is probably a simple question.  If I am looping through a recordset
and
> encounter a null value, I want to skip it, but I can't do that.  Here are
> the things I've tried:

> if IsNull(rs.fields(0)) then
>     'Do code block
> end if

> if rs.fields(0) = "" then
>     'Do code block
> end if

> if rs.fields(0) = chr(0) then
>     'Do code block
> end if

> Any other ideas?

> Thanks.
> -Steve


 
 
 

IsNull() Alternative

Post by Dave Keigha » Mon, 09 Apr 2001 11:29:58



Quote:> This is probably a simple question.  If I am looping through a recordset
and
> encounter a null value, I want to skip it,
...
> Any other ideas?

Steve
Null values in recordsets have caused me no end of grief. There are a few
tricks around for handling them here's one of mine: I use Format. It adds a
small hit to what you're doing and there are other methods but I use it and
like it.
Strings can have one or two sections separated by a semicolon (;). The first
section applies to string data, the second to Null values and zero-length
strings ("").
Numbers can have from one to four sections separated by semicolons. The
first section applies to positive values, the second to negative values, the
third to zeros, and the fourth to Null values.
Now all you need to do is evaluate what you've formatted.
It's further explained in the Help :) file.

Another method is to add a null string to the field and evaluate it. I don't
use it but I've seen it mentioned before and it seems to be popular. I'm
pretty sure I've got this right ... some-one will correct me if not :-)
if ("" & rs.fields(0)) = "" then
    'Do code block
end if

GL
Dave

 
 
 

IsNull() Alternative

Post by Richa » Mon, 09 Apr 2001 12:34:30


I am not sure what you are trying to do.

        If IsNull(rs.fields(0)) = False then
                'do code block
        end if

or

        If Not IsNull(rs.fields(0)) then
                'do code block
        end if

should work fine to detect if a field is not null.

If you are working with string fields, a common technique is the
following:

        if (rs!Lastname & "") <> "" then
                ' code here to do something with string
        end if

The expression
        rs!Lastname & ""

will always return a string, even if the recordset field is Null.

If you are working with numeric,date and bolean fields, use the IsNull
statement.

I am not sure why you are identifying fileds by number rather than
name - is this a routine to scan an unknown table?

If so, you may need to check rs.fields(0).Type (or rs(0).Type) to see
what kind of variable is stored in the field before doing the test.
For example:

    for i = 0 fo rs.fields.count - 1
        Select Case rs.fields(i).type

            Case adVarWChar, adLongVarWChar
                ' Field is text or memo
                If trim( rs.fields(i) & "" ) <> ""  then
                    'Field has text information - do code block
                end if

            Case adDate
                if Not IsNull( rs.fields(i) ) then
                     ' code to process Date
                 end if

            Case Else
                ' Field is some other kind of data
            if Not IsNull( rs.fields(i) ) then
                     ' do code block
                 end if

        End Select
    next i

Quote:>Hey...

>This is probably a simple question.  If I am looping through a recordset and
>encounter a null value, I want to skip it, but I can't do that.  Here are
>the things I've tried:

>if IsNull(rs.fields(0)) then
>    'Do code block
>end if

>if rs.fields(0) = "" then
>    'Do code block
>end if

>if rs.fields(0) = chr(0) then
>    'Do code block
>end if

>Any other ideas?

>Thanks.
>-Steve

 
 
 

IsNull() Alternative

Post by Dave Keigha » Mon, 09 Apr 2001 12:44:24


Steve
I re-read your question and *think* what your looking for may be the Not
Operator which is used to perform logical negation on an expression.

Quote:> If I am looping through a recordset and encounter
> a null value, I want to skip it, but I can't do that.
> if IsNull(rs.fields(0)) then
>     'Do code block
> end if

Your code above prcesses the code block if the field value is Null.

This code will process the code block *unless* the field is Null.
if Not IsNull(rs.fields(0)) then
    'Do code block
end if

?? what you were looking for ??
Dave

 
 
 

1. should Isnull(sum(blah),0) and sum(isnull(blah,0)) be the same?

Contrary to what I know about SQL, given this query:

select symbol, isnull(sum(currs),0) as Test1, sum(isnull(currs,0)) as
Test2 from beast_borrowscompile where symbol = 'Alpha'
group by symbol

and this set of records:
Alpha   NULL
Alpha   -408304
Alpha   NULL
Alpha   NULL
Alpha   NULL
Alpha   -107337
Alpha   NULL
Alpha   -2691
Alpha   -10426
Alpha   -1282
Alpha   -60391
Alpha   NULL
Alpha   NULL

I find that Test1 and Test2 give the same number (-590431).  

I think I remember reading somewhere that on NULLS, Sum treats them
like 0s.
Any ideas?  My thought was that Test1 would be 0, since null + numbers
= null.

2. Error 602 and service pack 3

3. isnull() truncates values

4. Apostrophe searches

5. Dynamic SQL with ISNULL

6. reading in a tab delimited file and sending to db

7. convert column and add isnull function

8. Browse SQL Servers with SQLBrowseConnect()

9. IsNull ???

10. isnull() function equivelence in OLEDB jet 4.0

11. Little problem with ISNULL

12. Problem with isnull function

13. Urgent !! ISNULL variant for a text string