Curious behavior of an if statement

bknight
BronzeLounger
Posts: 1463
Joined: 08 Jul 2016, 18:53

Curious behavior of an if statement

Post by bknight »

I was running a for loop and had this if statement in it
For I = 1 to Rs!NumRec
iI Fld1 <> I then
Rs.Edit
Fld1=I
rs.Update
next I
There are a couple of other lines in the code but none are pertinent to my question
Fld1 contains essentially the Dbs number from 1 to Recordset.
I import records from Excel and there are no numbers in the spreadsheet.
So when the routine encounters the new records that field(Fld1) is Null.
The routine skipped over the new records not updating Fld11 to I. Now a Null does not equal to any number, so why did it skip over the new records?
I Changed the If statement to If IsNull(Fld1) Then, everything ran well.

User avatar
HansV
Administrator
Posts: 78788
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Curious behavior of an if statement

Post by HansV »

Null is not a value like 0 or 1. It is the absence of a value. For this reason, you cannot compare Null to a value

Null = 0 evaluates to Null, not to True (nor to False)
Null > 0 evaluates to Null
Null < 0 evaluates to Null
Null <> 0 evaluates to Null
Null = Null evaluates to Null
Null <> Null evaluates to Null

So you must use IsNull(expression), or in SQL expression Is Null to test for Null.
Best wishes,
Hans

bknight
BronzeLounger
Posts: 1463
Joined: 08 Jul 2016, 18:53

Re: Curious behavior of an if statement

Post by bknight »

Ok.