Invalid use of a null

[CLevent21]

I made this VBA code in an Access form but I am getting an error when I am at the last record and hit the Command button again I get a Run-Time error 94 invalid use of a null. The code is down below any help will be appreciated. [email protected]


Dim dtIn As Date
Dim dtOut As Date
Dim cClass As Variant
Dim dClass As Variant
Dim tOut
Dim tIn
Dim tTotal




dtIn = CDate([IN]) + CDate([Time In])




dtOut = CDate([Done]) + CDate([Time Done])

If dtOut >= dtIn Then

[Total Time Hours] = Int(DateDiff("n", dtIn, dtOut) / 60)
[Total Time Minutes] = DateDiff("n", dtIn, dtOut) Mod 60
ElseIf dtIn Or dtOut = DateDiff("n", dtIn, dtOut) Then
MsgBox "Invalid Date in Date Out", vbCritical, "Date Out"

On Error GoTo Err_Command52_Click


DoCmd.GoToRecord , , acNext

Exit_Command52_Click:
Exit Sub

Err_Command52_Click:
MsgBox Err.Description
Resume Exit_Command52_Click

[Bigley]

I didn't really look at your code to be honest, but if you know you are at the last record why don't you just disable the command button so that you cannot click it again.

So every time you move down the record set check to see if you are at the last record. I imagine that you are trying to assign a value from a non existant field in a non existant record (i.e. the one after the last record) to a variable, therefore - Invalid use of Null

[pardede]

The error occurs when the code tries to set a value of a variable to NULL while the datatype of that variable don't want it. I guess what happens is after the last record, all columns are NULL so you are trying to set dtIn and dtOut to NULL:

dtIn = CDate([IN]) + CDate([Time In]) 'is null+null
dtOut = CDate([Done]) + CDate([Time Done]) 'is null+null

Perhaps you could try this:

dtIn = CDate([IN]) + CDate([Time In]) + 0
dtOut = CDate([Done]) + CDate([Time Done]) + 0

I don't know if this works but this avoids getting NULL as result of the sum. I know it works with strings in a construction like:

txtBox1.Text = [field] & ""

If it doesn't work, you'll indeed need to disable the cmdbutton when you reach last record, or check first before you do the sum:

If rs.EOF then Exit Sub