Add times

[Igorin]

Hello,

I have a table where I store the times (in hours and minutes, 17:23) it
takes for individual activities to get done. Each activity has its own column
in the datasheet.

These activities are all linked to a Report Nr. Sometimes in a Report Nr for
one activity three different times will be reported because the same acity
was done in three different areas or in the same area but at different times
of the day. Each time the activity appears in the report it's stored in its
own row.

This means that the values for the activity fields are linked to the Work
Area Nr and also to the Report Nr.

I want to add the total times for each activity for a single Report Nr. This
query would be in a subform, in the form where the rest of the info for each
report would be viewed.

I would greatly appreciate any help.

 

[Jeff Boyce]

Are the "times" you are storing text values or are you attempting to use the
Access Date/Time data type?

If the former, you'll need to build a parsing function that turns 17 hours
and 23 minutes into total minutes. Then you'd add the minutes and use
another parsing function to display the total back in hhmm format.

If the latter, don't! The Access Date/Time data type stores "point-in-time"
values, not "duration" values.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Igorin]

Hi, Jeff,

Thanks for the response.

I assigned the fields the Date/Time type because I will upload data from a
daily Excel report I receive, and there the amount of time appear in the
following format, 2:23 (meaning that the duration was 2 hours and 23 minutes).

There are so many columns and rows in the Excel file with the data in this
format that I was hoping that I could upload it like this.

Do I still need to transform all this data into minutes and then back into
hh:mm? What is this "parsing" function? and where do I use it, in Excel or
can I use it directly in Access?

 

[Jeff Boyce]

"Parsing" is just a term of art, meaning to process into the form you want.
There is no "parsing" function, you have to build it -- try checking at
mvps.org/access for examples.

You will cause yourself considerable headaches if you are putting "HH:MM" (a
text string) into an Access Date/Time field. Whether you realize it or not,
Access is storing a DATE and a TIME, no matter what it's displaying.

If you need to add up time (i.e., duration), you need to have all the times
in the same unit of measure (for example, minutes).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Igorin]

Thanks again, Jeff. I'll work on it.

--
igor

"Parsing" is just a term of art, meaning to process into the form you want.
There is no "parsing" function, you have to build it -- try checking at
mvps.org/access for examples.

You will cause yourself considerable headaches if you are putting "HH:MM" (a
text string) into an Access Date/Time field. Whether you realize it or not,
Access is storing a DATE and a TIME, no matter what it's displaying.

If you need to add up time (i.e., duration), you need to have all the times
in the same unit of measure (for example, minutes).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP