Adjust Date if Start time -> Stop time is overnight

  • Thread starter Thread starter Renraf
  • Start date Start date
R

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?
 
What does "doesn't work" mean in this context? Do you get an error? If so,
what's the error message?
 
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.
 
Try putting that in the form's BeforeUpdate event, not the BeforeUpdate
event for the text box.
 
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?
Click to expand...

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.
 
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).
 
Nevermind. I thought it was storing a date because of the way it handled the
subtraction. Apparently it does no such thing.
 
Back
Top