iif expression based on time difference txtbox??

[mcamp]

Hi, I have an entry form that uses a custom function to calculate hours and minutes between two times. Someone enters in a beginning time(timein), an end time (timeout) and a 3rd textbox populates with the hours and minutes using the custom function.

edit: the value of the calculated time difference is then put in a linked textbox (txtTotalHours) via VBA, to be stored in the table itself. txtTotalHours holds an actual value, not an expression

I am trying to make another textbox that has an expression to alert the person entering if the total hours is greater than 12 -

The idea is that if the calculated hours is greater than twelve then whoever is keying the data messed up the Time in or time out

Everything I have tried

iif([txtTotalHours] Between #0# AND #12#,"no error","error")
iif([txtTotalHours] Between #00:00# AND #12:00#,"no error","error")
iif([txtTotalHours] Between "00:00" AND "12:00","no error","error")
iif([txtTotalHours] > "12:00","error","no error")
iif([txtTotalHours] > #12:00#,"error","no error")
iif(Left([txtTotalHours],2) > "12","error","no error")

none are working. anyone know why?

 

[xenou]

What is in txtTotalHours and how is it formatted?

For what it's worth, textboxes always have text in them. You need to cast this to a double (decimal) value or a date (time) value. Then compare.

I.e. (perhaps),

If TimeValue(me.txtTotalHours) > Timevalue("12:00:00") then
msgbox "Foo"
End If

 

[mcamp]

both the field on the BE and txtbox format are Short time, both with same input mask.

it looks like the timevalue would work, but from VBA it updates all records on the continuous form with "error" if just 1 is greater than 12.

anyway I can use the TimeValue function in an expression?

 

[mcamp]

=IIf(TimeValue([txtTotalHours])>TimeValue("12:00:00"),"error","no error")

thanks!