Preventing negative total time

[Tom Tripicchio]

In my form I have ID#, Date, Timein and Timeout, total time.

I am using the medium time format. I want to prevent the total time from
being a negative time. Example is: time in 8:00 am and time out time 3:00 pm
total time should be 7 hrs, however if a person enters 8:00 am for time in
and 3:00 am for time out I will receive a negative number. I want to alert
the person who is entering the time that you cannot enter this time as it is
before the time in.

Sorry for the confusion.

Tom

 

[Jeff Boyce]

Tom

From your description, you have separate fields for date and time (and
separate timein and timeout fields). Why?

Access offers a date/time datatype. Why not use that and let Access record
the fact that the start time was 8am YESTERDAY, and the stop time was 3am
TODAY. When you do the math on date AND time, you get the correct answer.

When you do the math on YOUR time values, you get negative values!

And by the way, there is rarely a good reason for storing the "totaltime"
(which is a calculated value). What happens if someone realizes the start
or stop time for a record was entered incorrectly and fixes it? Now your
"total time" is no longer correct! Use a query to calculate the difference
instead of trying to store it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brendan Reynolds]

In my form I have ID#, Date, Timein and Timeout, total time.

I am using the medium time format. I want to prevent the total time from
being a negative time. Example is: time in 8:00 am and time out time 3:00
pm total time should be 7 hrs, however if a person enters 8:00 am for time
in and 3:00 am for time out I will receive a negative number. I want to
alert the person who is entering the time that you cannot enter this time
as it is before the time in.

Sorry for the confusion.

Tom

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Time in can't be less than time out"
Cancel = True
End Sub

Could also be done with a table validation rule.

 

[Brendan Reynolds]

Private Sub Form_BeforeUpdate(Cancel As Integer)
MsgBox "Time in can't be less than time out"
Cancel = True
End Sub

Could also be done with a table validation rule.

Oops! Left out the test ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.TimeIn < Me.TimeOut Then
MsgBox "Time in can't be less than time out"
Cancel = True
End If

End Sub

 

[Tom Tripicchio]

Thanks Brendon,

I will give it a try.

Oops! Left out the test ...

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.TimeIn < Me.TimeOut Then
MsgBox "Time in can't be less than time out"
Cancel = True
End If

End Sub

 

[Tom Tripicchio]

Thanks for the info. There are times when people forget to put in their time
and therefore have to go back and enter the info. I do track what times
those entries have been placed in the form. I do agree that I store data
that I could receive from a query and that is something that I need to work
on.

 

[Tom Tripicchio]

I was able to make it work when I used ELSE instead of cancel. Cancel did
not work. The message does show up, however you can continue to add the info
even after the message pops up. Is there a way to prevent them from going
any further in the form?

Thanks, Tom