Sum of total hours

[Johnny]

Hello everybody

I need to keep record of flight time and cargo.
On a form i have a field for flight time input and another for cargo.
Most of flight times are like 45H30, 75H20, etc.
The field format is short time and on the tabel is date. When inputing
does values Access gives error because time is over 24hours.
The fact is i need to store that value as is (45H30) and in the end of
moth calculate a grand total presented like
358H45.

Someone could help??
Thanks

 

[Allen Browne]

So 45H30 means 45 hours and 30 minutes?

There are 2 issues here: how to store the data, versus how to interface
(display/enter) the data. If you want to treat the data as numeric (e.g.
2H45 + 4H30 = 7H15), then it would be best to use a Number field to store
the whole minutes. You can then *display* it in a text box bound to an
expression like this:
=[Minutes] \ 60 & Format([Minutes] Mod 60, "\H00")

If you want to enter it that way, you would use an unbound text box, and in
its After Update event procedure, use Instr() to locate the "H". Take the
Val() of the first part, multiply by 60 and sum the Val() of the Mid() after
the "H". Assign the result to your Minutes field.

While it would be possible to store the odd values into a text field, the
math would be inefficient and clumsy: it's actually numeric data, and best
stored that way.

HTH

 

[Johnny]

Hi Allen

Thank you for you answer.
Because users expect to see the input field like XXHXX or XX:XX,
that's the way they work with flight time, my idea was to create a
date field with short date format, the problem arises when they need
to insert more than 24 hours on that field (eg. 42H50 fourty two hours
and fifty minutes), the access won´t let.
So, my question is to get a way of let user input on the described
format and allow calculations of total hours.
If I define on the table the field value as numeric, how can I grab it
on form like 42H50 (text) and store it as numeric.
Probably the sugestions you sent me are clear for you, but yet not so
clear for me.
Can you help me on clarifying the solution?

Thanks

 

[Allen Browne]

You cannot use a date/time field, and have it display hours > 24.

You can use 2 Number fields if you wish.
Put 2 text boxes side-by-side, so they show up on your form like this:
[ ]H[ ]
User enters the 2 numbers.

(I'm assuming you didn't understand what I suggested about using an unbound
text box to allow the user to enter 42H50 and have its AfterUpdate event
procedure figure out the 42*60+50 and store the result.)

 

[Johnny]

Allen hello again,

Thankyou very much, you solve my problem.
Now I have a table with a number representing the hours an minutes
inputed on separated fields.
If I need to edit the number on the table how can I invert the process
and have the number displayed on another form on separated fields to
edit and than aplly the formula again to store.

Exemple:

I have on the table number 7230 representing the input of [120]H[30].
If need to edit 7230 how can I transform it in [120]H[30], in order to
change the value from 120 to 130 and store again as number.

I hope I'm not confusing,
thanks

 

[Johnny]

Allen hello

I already have number converted to hours and minutes with:

FieldHours=(Format([intHours]\60;"0")) for hours
FildMinutes=Format([intHours] MOD 60;"00") for minutes, but I'm not
able to edit both values.

Is there something wrong??

Thanks

 

[Allen Browne]

So in your table, you have 2 fields of type Number, with names intHours and
intMinutes. Now on your form, you have 2 text boxes bound to these 2 fields?
Is that what you've done?

If so, I'm not sure where you are using these 2 expressions.

What I originally suggested was *one* field in the table for storing
minutes, and an unbound text box where the user can enter 42H30. You use the
afterupdate event of this text box to calculate the minutes and assign it to
the (hidden) field. This kind of thing:

Private Sub txtTime_AfterUpdate()
Dim strInput As String
Dim lngPos As Long
Dim lngHours As Long
Dim lngMinutes As Long

strInput = Nz(Me.txtTime, vbNullString)
lngPos = InStr(strInput, "H")
If lngPos > 0 Then
lngHours = Val(Left$(strInput, lngPos - 1))
lngMinutes = Val(Mid$(strInput, lngPos + 1))
Me.Minutes = lngHours * 60 + lngMinutes
End If
End Sub

 

[Johnny]

Thank you Allen, I read carefully your previous posts, and I end up
with a soluction similar to your exemple.

Cheers