If Condition

[rigby]

Hi
I am trying to attempt some code. I want to have a condition where, if it
isnt met, access will pop up an error message explaining why it isnt allowed.

I have these controls involved:
[Start Date], [End Date], [Time on Duty], [Time off Duty].
when 'end date' = 'start date', i want time on duty to HAVE to be less than
'time off duty. i have another control which is working great which
calculates total hours worked like this:
=Round(IIf((DateDiff("n",[Time on Duty],[Time off
Duty])/60)<0,(DateDiff("n",[Time on Duty],[Time off
Duty])/60)+24,(DateDiff("n",[Time on Duty],[Time off
Duty])/60)),2)+((DateDiff("d",[Start Date],[End Date])*24))

it doesnt work when an incorrect time has been put in the 'time on duty'.
how may i create a condition for this?

Thank you

Rigby

 

[Lee Robinson]

Your IIF function looks fine. No function will work if it gets incorrect
input. I can understand wanting to tell the user if they enter unreasonable
information. I would do the checking in the BeforeUpdate event:

Private sub Time_on_Duty_BeforeUpdate(Cancel as integer)
If IsNull(Me.[Time off Duty]) then
doevents ' don't complain if time off has not been entered yet
Else
If (DateDiff( "n",[Time on Duty],[Time off Duty] )/60 ) < 0 then
Msgbox "The Time On Duty (" & me.[Time on Duty] _
& ") is later than the Time Off Duty (" &
Me.[Time off Duty] & ")." _
, vbcritical _
, "Checking Time On Duty"
Cancel = true
End If
End If
End Sub

Private sub Time_off_Duty_BeforeUpdate(Cancel as integer)
If IsNull(Me.[Time on Duty]) then
doevents ' don't complain if time on has not been entered yet
Else
If (DateDiff( "n",[Time on Duty],[Time off Duty] )/60 ) < 0 then
Msgbox "The Time On Duty (" & me.[Time on Duty] _
& ") is later than the Time Off Duty (" &
Me.[Time off Duty] & ")." _
, vbcritical _
, "Checking Time Off Duty"
Cancel = true
End If
End If
End Sub

Lee Robinson