Invalid Use of Null - After Update

[Guest]

I am trying to write a somewhat complex "AfterUpdate" event for a combo box
[cboRenewalConditions] that designates the terms of renewal for a given legal
agreement.

Several date fields are involved:
[EffectiveDate] - Agreement effective date
[InitialTermExp] - The initial term expiration date
[CurrentTermExp] - The current term expiration date

I'm getting an "invalid use of null" error when I make a change to the combo
box.

Here's the code I used for the AfterUpdate event:

If Not IsNull(Me![EffectiveDate]) And Me![Expired/Terminated] = 0 And
Me![cboRenewalConditions] = 1 And DateSerial(Year(Now()),
Month([EffectiveDate]), Day([EffectiveDate]) <= Now()) Then
Me![CurrentTermExp] = DateSerial(Year(Now()) + 1, Month([EffectiveDate]),
Day([EffectiveDate]))
ElseIf Not IsNull(Me![EffectiveDate]) And Me![Expired/Terminated] = 0 And
Me![cboRenewalConditions] = 1 And DateSerial(Year(Now()),
Month([EffectiveDate]), Day([EffectiveDate]) > Now()) Then
Me![CurrentTermExp] = DateSerial(Year(Now()), Month([EffectiveDate]),
Day([EffectiveDate]))
ElseIf Not IsNull(Me![InitialTermExp]) And Me![cboRenewalConditions] = 2 And
DateSerial(Year(Now()), Month([InitialTermExp]), Day([InitialTermExp]) >=
Now()) Then
Me![CurrentTermExp] = Me![InitialTermExp]
Else
Exit Sub
End If

Please help!

 

[Duane Hookom]

You are checking for some fields that might be null but not all. Have you
tried to step through the code?

Also
DateSerial(Year(Now()), Month([EffectiveDate]), Day([EffectiveDate]) <=
Now())
do you realize that Day([EffectiveDate]) will always be less than Now()? I'm
not sure what you are attempting to do with this expression.

 

[Guest]

Thanks for your suggestion -- it makes sense. In the meantime, I figured out
a different way to set up this AfterUpdate function using "Select Case."

You are checking for some fields that might be null but not all. Have you
tried to step through the code?

Also
DateSerial(Year(Now()), Month([EffectiveDate]), Day([EffectiveDate]) <=
Now())
do you realize that Day([EffectiveDate]) will always be less than Now()? I'm
not sure what you are attempting to do with this expression.
--
Duane Hookom
MS Access MVP

I am trying to write a somewhat complex "AfterUpdate" event for a combo box
[cboRenewalConditions] that designates the terms of renewal for a given
legal
agreement.

Several date fields are involved:
[EffectiveDate] - Agreement effective date
[InitialTermExp] - The initial term expiration date
[CurrentTermExp] - The current term expiration date

I'm getting an "invalid use of null" error when I make a change to the
combo
box.

Here's the code I used for the AfterUpdate event:

If Not IsNull(Me![EffectiveDate]) And Me![Expired/Terminated] = 0 And
Me![cboRenewalConditions] = 1 And DateSerial(Year(Now()),
Month([EffectiveDate]), Day([EffectiveDate]) <= Now()) Then
Me![CurrentTermExp] = DateSerial(Year(Now()) + 1, Month([EffectiveDate]),
Day([EffectiveDate]))
ElseIf Not IsNull(Me![EffectiveDate]) And Me![Expired/Terminated] = 0 And
Me![cboRenewalConditions] = 1 And DateSerial(Year(Now()),
Month([EffectiveDate]), Day([EffectiveDate]) > Now()) Then
Me![CurrentTermExp] = DateSerial(Year(Now()), Month([EffectiveDate]),
Day([EffectiveDate]))
ElseIf Not IsNull(Me![InitialTermExp]) And Me![cboRenewalConditions] = 2
And
DateSerial(Year(Now()), Month([InitialTermExp]), Day([InitialTermExp]) >=
Now()) Then
Me![CurrentTermExp] = Me![InitialTermExp]
Else
Exit Sub
End If

Please help!