Help with Invalid use of Null Error

[stockwell43]

Hello,

I have some code that subtracts dates to enter a weekday difference to
display that number in a turn time field. However, the code seems to work
fine except with I delete the date in one field I get the following error:

Run time error 94

Invalid use of Null

This is the code behind the field after the date is entered:
Call cmdTurnTime_Click

This is the code it's calling:

Private Sub cmdTurnTime_Click()
Me.Text77 = NumWeekdays([OriginalDate], [CompleteDate] - 1)
End Sub

If I remove the -1 it works fine no matter what except for some reason it
starts from the date entered day and I need it to start on the next day. Ex:
If I enter 01/20/2009 if the user completed the report on 02/03/2009 the code
shows 11 days for turn time instead of 10

So I thought if I subtract one day it would be fine and it does work fine
except the error pops up when the date is removed and you jump to another
field. Problem is, if someone clicks Debug, my code is exposed. Is there some
way of correcting this??

Thank you!!!!!

 

[John Spencer (MVP)]

You could try the following

Private Sub cmdTurnTime_Click()
IF IsNull(OriginalDate] or IsNull(CompleteDate) THEN
me.Text77 = null
'or leave the above line out to keep text77 from changing
ELSE
Me.Text77 = NumWeekdays([OriginalDate], [CompleteDate] - 1)
END If
End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Jeff Boyce]

So if your data is incomplete (i.e. missing one/more dates), what do you
want to have happen?

Regards

Jeff Boyce
Microsoft Office/Access MVP