Compare BeginTime and EndTime

[shylock]

I have a Down Time table with a BeginTime field and an EndTime field. These fields are separate from the BeginDate field and EndDate field and are formatted as Date/Time fields. When entering the down Time on a form I want to ensure that the EndTime entered is later than the BeginTime. MWhen the "Save" button is clicked, I perform a check to make sure BeginTime is greater than EndTime as shown below:



Select Case Me.txtEndTime
Case (IsNull(TimeValue(Me.txtEndTime)) Or (TimeValue(Me.txtEndTime) = #12:00:00 AM#))
myMsg = MsgBox("You must enter a valid End Time.", vbInformation, "Invalid End time")
Cancel = True
DoCmd.GoToControl "txtEndTime"
' Case (TimeValue(Me.txtEndTime) < TimeValue(Me.txtBeginTime))
Case (StrComp(Me.txtEndTime, Me.txtBeginTime) = -1)
myMsg = MsgBox("End Time must be greater than Begin Time", vbInformation, "Invalid End time")
Cancel = True
DoCmd.GoToControl "txtEndTime"
Case (Me.txtEndTime < Me.txtBeginTime) And (DateValue(Me.txtEndDate) <= DateValue(Me.txtBeginDate))
myMsg = MsgBox("End Time must be greater than Begin Time", vbInformation, "Invalid End time")
Cancel = True
DoCmd.GoToControl "txtEndTime"
End Select

When I debug the code it just falls through even though I enter an End time that is less than the begin time. In the immediate window the StrComp function returns 1 in stead of -1. The first Case is Ok when I don't enter an EndTime or skip the txtEndTime control that has a time of 0:00. the Third Case doesn't catch an invalid EndTime either.

What am I doing wrong? Please help.

Thank you.

[apr pillai]

Combine both Date & Time together on both side (EndDate_PlusTime - Begin_Date_PlusTime) to make a simple comparison.

[ranman256]

you dont want 2 fields, 1 for date and 1 for time.
the datetime field holds both...this allows for getting the difference txtDateTime1 - txtDateTime2

if you already have them separate, combine them in a query. Date1 & " " & Time1
you cannot do time math on separate fields. it must be:
if dateTime1 < dateTime2

[shylock]

apr pillai:

Thnk you for your response. I replaced the case with the StrComp with the following:

Case ((Me.txtEndDate + Me.txtBeginTime) - (Me.txtBeginDate + Me.txtBeginTime) <= 0)

In the immediate window it returned 0 but in debugging, the case statement didn't catch it. Should I use the StrComp ?

[apr pillai]

apr pillai:

Thnk you for your response. I replaced the case with the StrComp with the following:

Case ((Me.txtEndDate + Me.txtBeginTime) - (Me.txtBeginDate + Me.txtBeginTime) <= 0)

In the immediate window it returned 0 but in debugging, the case statement didn't catch it. Should I use the StrComp ?

I see a Problem with the Time part:

Case ((Me.txtEndDate + Me.txtEndTime) - (Me.txtBeginDate + Me.txtBeginTime) <= 0)

[shylock]

Thanks apr pillai,

My bad! I'm not the best typist. I was able to solve the problem with the code below:

LcmpTime = (StrComp(Me.txtEndDate + Me.txtEndTime, Me.txtBeginDate + Me.txtBeginTime))

If LcmpTime <= 0 Then
myMsg = MsgBox("End Time must be greater than Begin Time", vbInformation, "Invalid End time")
Cancel = True
DoCmd.GoToControl "txtEndTime"
End If

How do I mark this thread as "Solved"