checking of time entry falls between time increment

[eric]

Hello,

I ran into a dilemma involving an user input time value
and evaluating this input in the VBA portion of Access
2k. When an user inputs a time value (eg "5:30 PM"), I
want vba to evaluate whether it falls between "12:00 PM"
and "1:00 PM" [lunch] or falls after "5:00 PM" [after the
office closes]. Based on the time input, I will execute a
certain module.

However, I couldn't get the vba in place to carry out this
kind of check.

This is what I have in place:
_____________________________________________________
tempTime = Format(UserTimeInput, "h:nn AM/PM")
If tempTime >= "12:00 PM" And tempTime < "1:00 PM" Then
call RunLunchModule
elseif tempTime >= "5:00 PM" Then
call RunAfterHourModule
else
call RunOfficeHourModule
End If
_____________________________________________________
this doesn't seem to work right.

Can anyone help me or offer pointers?

thanks

 

[Allen Browne]

The problem is with the data types. Access is performing a string
comparison, and you need a date/time comparison.

Dim strTimeInput As String
Dim dtTimeInput As Date

strTimeInput = InputBox("Time please:")
If IsDate(strTimeInput) Then
dtTimeInput = CDate(strTimeInput)
If dtTimeInput >= #12:00:00# And dtTimeInput < #13:00:00# Then
Call RunLunchModule
ElseIf dtTimeInput > #17:00:00# Then
Call RunAfterHourModule
Else
Call RunOfficeHourModule
End If
Else
MsgBox "Invalid time"
End If


You may also want to check the time is not before 8am, and that it does not
contain a date component.