Not recognizing nulls

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

The code is not updating the Next Audit Date for nulls.

I'm trying to pull accounts with an Exception Rate of
either 0 or non-existent. I need to be able to
distinguish between both of these so I cannot turn the
nulls into 0. Exception Rates are percentages.


If [MatrixClass] = 2 Then
If [ExcptRate] = 0 Or [ExcptRate] = Null Then
[next_audit_date] = DateSerial(Year(last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))
 
Hi,


A comparison with a NULL returns NULL (more precisely, Unknown), and
since NULL is not TRUE, it falls in the ELSE part of the If:


If Null = Null then
Msgbox "this is a bug if this is displayed...."
Else
MegBox "it is not true that even NULL= NULL. "
End If


Understand there are 3 logical values: TRUE, FALSE and NULL. So, Not True
is a case where it is either False, either Null.


To test about a Null, in VBA, use IsNull( argument ).



If IsNull( Null ) OR ... Then
...


Hoping it may help,
Vanderghast, Access MVP
 
I already have an ELSE statement in the code that adds --
12 -- months to accounts not meeting the stated
requirements. I need further direction to pull AdvAssets
with both values of 0 and null in order to add -- 36 --
months.

If [ExcptRate] = 0 Then or If [ExceptRate] = null
[next_audit_date] = DateSerial(Year(last_audit_date), _
Month([last_audit_date]) + 36, Day
([last_audit_date]))

ElseIf [ExcptRate] <= 0.1 Then
[next_audit_date] = DateSerial(Year(last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))


Else: [next_audit_date] = DateSerial(Year(last_audit_date)
_
, Month([last_audit_date]) + 12, Day
([last_audit_date]))
End If
End If





-----Original Message-----
Hi,


A comparison with a NULL returns NULL (more precisely, Unknown), and
since NULL is not TRUE, it falls in the ELSE part of the If:


If Null = Null then
Msgbox "this is a bug if this is displayed...."
Else
MegBox "it is not true that even NULL= NULL. "
End If


Understand there are 3 logical values: TRUE, FALSE and NULL. So, Not True
is a case where it is either False, either Null.


To test about a Null, in VBA, use IsNull( argument ).



If IsNull( Null ) OR ... Then
...


Hoping it may help,
Vanderghast, Access MVP

The code is not updating the Next Audit Date for nulls.

I'm trying to pull accounts with an Exception Rate of
either 0 or non-existent. I need to be able to
distinguish between both of these so I cannot turn the
nulls into 0. Exception Rates are percentages.


If [MatrixClass] = 2 Then
If [ExcptRate] = 0 Or [ExcptRate] = Null Then
[next_audit_date] = DateSerial(Year (last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))
Click to expand...


.
Click to expand...
 
Hi,


Try:


IF ExcprRate=0 OR IsNull(ExcptRate) THEN
...


Hoping it may help,
Vanderghast, Access MVP


I already have an ELSE statement in the code that adds --
12 -- months to accounts not meeting the stated
requirements. I need further direction to pull AdvAssets
with both values of 0 and null in order to add -- 36 --
months.

If [ExcptRate] = 0 Then or If [ExceptRate] = null
[next_audit_date] = DateSerial(Year(last_audit_date), _
Month([last_audit_date]) + 36, Day
([last_audit_date]))

ElseIf [ExcptRate] <= 0.1 Then
[next_audit_date] = DateSerial(Year(last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))


Else: [next_audit_date] = DateSerial(Year(last_audit_date)
_
, Month([last_audit_date]) + 12, Day
([last_audit_date]))
End If
End If





-----Original Message-----
Hi,


A comparison with a NULL returns NULL (more precisely, Unknown), and
since NULL is not TRUE, it falls in the ELSE part of the If:


If Null = Null then
Msgbox "this is a bug if this is displayed...."
Else
MegBox "it is not true that even NULL= NULL. "
End If


Understand there are 3 logical values: TRUE, FALSE and NULL. So, Not True
is a case where it is either False, either Null.


To test about a Null, in VBA, use IsNull( argument ).



If IsNull( Null ) OR ... Then
...


Hoping it may help,
Vanderghast, Access MVP

The code is not updating the Next Audit Date for nulls.

I'm trying to pull accounts with an Exception Rate of
either 0 or non-existent. I need to be able to
distinguish between both of these so I cannot turn the
nulls into 0. Exception Rates are percentages.


If [MatrixClass] = 2 Then
If [ExcptRate] = 0 Or [ExcptRate] = Null Then
[next_audit_date] = DateSerial(Year (last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))
Click to expand...


.
Click to expand...
Click to expand...
 
That did the trick.... IsNull goes before instead of after
the field name.

Thanks!

-----Original Message-----
Hi,


Try:


IF ExcprRate=0 OR IsNull(ExcptRate) THEN
...


Hoping it may help,
Vanderghast, Access MVP


I already have an ELSE statement in the code that adds - -
12 -- months to accounts not meeting the stated
requirements. I need further direction to pull AdvAssets
with both values of 0 and null in order to add -- 36 --
months.

If [ExcptRate] = 0 Then or If [ExceptRate] = null
[next_audit_date] = DateSerial(Year (last_audit_date), _
Month([last_audit_date]) + 36, Day
([last_audit_date]))

ElseIf [ExcptRate] <= 0.1 Then
[next_audit_date] = DateSerial(Year (last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))


Else: [next_audit_date] = DateSerial(Year (last_audit_date)
_
, Month([last_audit_date]) + 12, Day
([last_audit_date]))
End If
End If





-----Original Message-----
Hi,


A comparison with a NULL returns NULL (more precisely, Unknown), and
since NULL is not TRUE, it falls in the ELSE part of
Click to expand...
the
If:
If Null = Null then
Msgbox "this is a bug if this is displayed...."
Else
MegBox "it is not true that even NULL= NULL. "
End If


Understand there are 3 logical values: TRUE, FALSE and NULL. So, Not True
is a case where it is either False, either Null.


To test about a Null, in VBA, use IsNull( argument ).



If IsNull( Null ) OR ... Then
...


Hoping it may help,
Vanderghast, Access MVP

The code is not updating the Next Audit Date for nulls.

I'm trying to pull accounts with an Exception Rate of
either 0 or non-existent. I need to be able to
distinguish between both of these so I cannot turn the
nulls into 0. Exception Rates are percentages.


If [MatrixClass] = 2 Then
If [ExcptRate] = 0 Or [ExcptRate] = Null Then
[next_audit_date] = DateSerial(Year (last_audit_date), _
Month([last_audit_date]) + 24, Day
([last_audit_date]))


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top