Checking null values in a field

[aman]

Hi Guys
The following code doesn't check null condition in Scannedby field. The scannedby has text data type. The code gives me all the data where scandate=20130722 but doesn't check that scanned by can't be blank.

rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic
j = rs.fields(0).Value

Thanks

 

[BlueIshDan]

Well, this is in your query.

scannedby <> null

But if you want the code for after the retreival:

    rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and scannedby <> null", cn, adOpenKeyset, adLockOptimistic
    
    If rs.RecordCount > 0 Then
        If Len(rs(0).Value & vbNullString) > 0 Then
            j = rs.Fields(0).Value
        End If
    End If

 

[pr2-eugin]

Null is not something you can compare with. Try,

rs.Open "SELECT Count(*) FROM BatchTBL2 WHERE scandate = 20130722 AND scannedby Is Not Null", cn, adOpenKeyset, adLockOptimistic
j = rs.fields(0).Value

What is 20130722 or scandate DataType. Looks like number but also a Date.

 

[BlueIshDan]

Also Try

rs.Open "select count(*) from BatchTBL2 where scandate=20130722 and [COLOR="Red"][B]scannedby IS NOT NULL[/B][/COLOR]", cn, adOpenKeyset, adLockOptimistic

 

[aman]

I tried it but it didn't work so i changed it to scannedby<>'' and it started working.

Any idea why?

 

[vbaInet]

I tried it but it didn't work so i changed it to scannedby<>'' and it started working.

Any idea why?

Your field is accepting the empty string as well. If you don't want this behaviour you should open the table in design view and change the Allow Zero Length property of the field to No. Then you need to update those fields containing "" to become Null.

If you don't do this you'll find yourself having to cover for both Null and "".