'Invalid use of Null' error

[raugust]

Hello:

I am trying to automate a membership status flag based on comparing today's date and a recorded expiry date. The expiry date control is on a sub-form. I have the following code in the OnLoad event of the master form:

Dim DateGap As Integer
While Me.CurrentRecord < Me.Recordset.RecordCount
If Not Me.NewRecord Then
DateGap = DateDiff("y", Date, Forms!PersonalMasterF!MemberSubF.Form.MemberExpire)
Else
DoCmd.GoToRecord , , acNext
End If
If DateGap >= 41 Then
Forms!PersonalMasterF!MemberSubF.Form.MemberActive = "Active"
ElseIf DateGap < 40 And DateGap >= 0 Then
Forms!PersonalMasterF!MemberSubF.Form.MemberActive = "Expiring"
ElseIf DateGap < 0 And DateGap > -60 Then
Forms!PersonalMasterF!MemberSubF.Form.MemberActive = "Expired"
ElseIf DateGap < -61 Then
Forms!PersonalMasterF!MemberSubF.Form.MemberActive = "Inactive"
Else:
Forms!PersonalMasterF!MemberSubF.Form.MemberActive = "Error"
End If
DoCmd.GoToRecord , , acNext
Wend

With debug pointing to the DateGap = statement I get the error 'Invalid use of Null'. As you can see, I've tried to trap any new records it might run into to avoid nulls in MemberExpire, and there are no null values in the MemberExpire field of the underlying table. I've also tried defining DateGap as Variant, which does not work at all. Thoughts?

 

[burrina]

Have you verified in your table, their are no Null Values?
Also, generally this handled via the Nz Function. Do any of the required fields allow null values?
HTH

 

[raugust]

There are no Null values in MemberExpire unless the code is operating on a new record, which in theory I've prevented. Is this what you eant by the use of the Nz function?

Dim DateGap As Integer
Dim Flag As Variant
While Me.CurrentRecord < Me.Recordset.RecordCount
Flag = Nz(Forms!PersonalMasterF!MemberSubF.Form.MemberExpire)
If Not Me.NewRecord Then
DateGap = DateDiff("y", Date, Flag)
Else
DoCmd.GoToRecord , , acNext
End If
If DateGap >= 41 Then
Flag = "Active"
ElseIf DateGap < 40 And DateGap >= 0 Then
Flag = "Expiring"
ElseIf DateGap < 0 And DateGap > -60 Then
Flag = "Expired"
ElseIf DateGap < -61 Then
Flag = "Inactive"
Else:
Flag = "Error"
End If
Forms!PersonalMasterF!MemberSubF.Form.MemberExpire = Flag
DoCmd.GoToRecord , , acNext
Wend

This produces an overflow error on the DateGap= statement.

 

[burrina]

Their is a Typo in your code;
Flag = Nz(Forms!PersonalMasterF!MemberSubF.Form.MemberExp ire) ' MemberExpire
Here are some Nz Examples:

Me.Something =Nz(Sum([Payment]),0) ' is a Example of the Nz Function.
Commission: CCur(Nz([BidAmount]*[ECommRate],0))

HTH

 

[raugust]

The typo's in my post but not my code. Still get the overflow error.

 

[Galaxiom]

In your thread title and first post you told us the error was "Invalid use of Null". In your later posts you say it was an "overflow". These are very different errors.

Presumably you fixed the Nulls that you did have in the data but for some reason didn't want to admit after it was pointed out to you. In future please be honest in your posts.

Once you fixed the Null the code failed at the overflow instead.

The DateGap variable is an integer. The largest value an Integer can hold is 32767. I expect you have a date in your data prior to 3 September 1924. All dates before this would cause an integer to overflow when compared with today's date.

Also please post code between code tags. This presents in a more readable font, retains indenting and prevents the space being automatically inserted in ordinary text if it reaches fifty characters without a break.

BTW
The entire goal of your procedure is wrong. You are storing a calculated value. The membership status should be calculated as required rather than being updated and stored via a procedure.

Even if you were going to do that it would be done in a query rather than thrashing through a form.

 

[JHB]

If you are counting the years between the dates then you need "yyyy" in the DataDiff function:
With only one "y" you are counting the days between the dates.
Normally the date with the oldest date is first.

For counting years:

DateDiff("yyyy", OldestDate, NewestDate)

For counting days:

DateDiff("y", OldestDate, NewestDate)

 

[raugust]

JHB, thanks for the tip.

Galaxiom, your suggestion regarding a query is spot-on - I have the whole deal set up with little grief using the Switch function. However, while I admit I need lots of advice on Access, and for that matter, using the forum, your assumption that I am being dishonest rather than making mistakes is out of line. You've obviously been around a long time, and perhaps you own the forum, but that's still not right.