Adjust Date if Start time -> Stop time is overnight

[Renraf]

I have a form with two Date fields, Start and Stop, that display as Medium
Time (e.g. 9:30 AM). I end up subtracting the difference between these times,
converting it to minutes, and comparing it to another value. The problem is
that if the user enters Start = 9:30 PM and Stop = 1:30 AM, I would like Stop
- Start = 4 hours (or 240 minutes). Instead, since the user is only entering
a time, Access is assuming they are on the same date, so the difference is
-20 hours.

How can I recognize that the Stop time is before the Start time, and add 1
day to the Stop time before doing my calculation? My thought was:

Private Sub Stop_BeforeUpdate(Cancel As Integer)
Dim NewStop As Date

If Me.Start > Me.Stop Then
NewStop = Me.Stop + 1
Me.Stop = NewStop
End If

But the line Me.Stop = NewStop doesn't work. What am I doing wrong, and how
should I go about this?

 

[Douglas J. Steele]

What does "doesn't work" mean in this context? Do you get an error? If so,
what's the error message?

 

[Renraf]

Run-time error '-2147352567 (80020009)':
The macro or function set to the BeforeUpdate or ValidationRule
property for this field is preventing Microsoft Office Access from saving
the data in the field.

 

[Douglas J. Steele]

Try putting that in the form's BeforeUpdate event, not the BeforeUpdate
event for the text box.

 

[John W. Vinson]

I have a form with two Date fields, Start and Stop, that display as Medium
Time (e.g. 9:30 AM). I end up subtracting the difference between these times,
converting it to minutes, and comparing it to another value. The problem is
that if the user enters Start = 9:30 PM and Stop = 1:30 AM, I would like Stop
- Start = 4 hours (or 240 minutes). Instead, since the user is only entering
a time, Access is assuming they are on the same date, so the difference is
-20 hours.

How can I recognize that the Stop time is before the Start time, and add 1
day to the Stop time before doing my calculation? My thought was:

Private Sub Stop_BeforeUpdate(Cancel As Integer)
Dim NewStop As Date

If Me.Start > Me.Stop Then
NewStop = Me.Stop + 1
Me.Stop = NewStop
End If

But the line Me.Stop = NewStop doesn't work. What am I doing wrong, and how
should I go about this?

Ideally you should store both the date and time in these fields, since you are
indeed working with different dates. #9/23/2009 02:00:00# is indeed four hours
after #9/22/2009 22:00:00#; you can use

DateDiff("n", [Start], [Stop])

to get the time between them, for four hours or for forty years as needed.

If you will *NEVER* have a time difference exceeding 24 hours, you could use

DateDiff("n", [Start], [Stop]) + IIF([Start] > [Stop], 1440, 0)

to add the number of minutes in a day to the negative difference.

 

[Renraf]

Douglas: I realized the same thing but moved it to the AfterUpdate event.
John: Since the field actually stores the date invisibly, I want to update
the field rather than just adjust my calculation. That way, any future
calculations will be correct. The method I posted is working fine now except
for 1 small hitch.

After I perform the calculation, the field displays as the full date. Does
anyone know how to specify a format of Medium Time? I know I could get
military time with Format(expr, "h:nn"), but I can't figure out how to get,
say, 7:23 PM (without a statement that subtracts 12 if the hour is over 12;
seems like there should be something built-in I can use).

 

[Renraf]

Nevermind. I thought it was storing a date because of the way it handled the
subtraction. Apparently it does no such thing.