parsing for addition

[imintrouble]

Hello people of Access Forums! In my report, i have a column where all of the hours a person has worked are recorded in the format of # Hours, # Minutes.

At the very bottom of this page, I have a box which I want to take the numbers (#) from the above mentioned format and add them together to get a total time spent. EX: 4 Hours, 7 Minutes + 3 Hours, 33 Minutes = Total Hours: 7 Hours, 40 Minutes

I need to parse the information from the Hours Worked field that is already there because it is in the string format from some code i had done a long time ago. What I believe is needed is for the value of hours to be parsed going down the entire report to be added together and output in the Total Hours box. it would also be for the best for the amount of minutes to be added together and that when they reach 60, to be added as a plus one to the total hours.

This has been a recurring problem for me and I would really like to just get rid of it as fast as i can.

[pbaldy]

I think you want a function that operates on each record and converts to a common denominator (seconds, minutes, etc). The basic tools you'll need are the Split() and Val() functions. It won't be too difficult if each record has the same sections (hours, minutes). It will be trickier if a record might just have minutes or something like that. Your other post said these contained seconds, so you'll want to clarify that too.

[June7]

Use string manipulation functions in expressions in query.

This will return the hours, assumes there will always be an hours part, even if 0 Hours.
Val([fieldname]) As HoursWork

The minutes are trickier, again assumes minutes part exists, even if 0 Minutes.
Val(Mid([fieldname], InStr([fieldname],",")+2) As MinutesWork

Refer to these constructed fields in report to do summary calcs.

Divide minutes by 60 to get decimal hours. All in one field would be like:

Val([fieldname]) + Val(Mid([fieldname], InStr([fieldname],",")+2)/60 As TimeWork

[imintrouble]

If Val returns the numbers in a data field, since mine is 4 hours, 7 minutes, wouldn't it return the 4 AND the 7?

Also, how would i be able to make my one text box in my report actually add together ALL of the total hours and minutes worked?

[June7]

Val stops when it encounters non-numeric values, so it returns 4 for the hours. You can test functions in the Immediate window of the VBA editor.

Use the constructed field in aggregate calc in textbox in group and/or report footer.
Sum([TimeWork])

[imintrouble]

so since the field the times will be coming from is called Hours_Work, how would i save each of those times as a variable to be added by Sum([TimeWork]) because I'm assuming the Sum([TimeWork]) is what will be in the Total Time text box.

[June7]

You create a field in query using suggested expression. That constructed field can be referenced by form or report just like any field. Access query designer will assign a name to the constructed field, like Expr1. You can replace that with anything you like.

Access Help has guidelines on building queries with calculations. Access2010 has a new datatype for tables - Calculated.