Have various cells that need to be summed up into a text box

[bradp1979]

Name: Access emp table.png  Views: 19  Size: 28.7 KB  ID: 21310" class="thumbnail" style="float:CONFIG" />
This is a form used to enter in the hours worked for each employee for any particular job. What I'm trying to do, is show a total for all the hours worked for the job in a text box under the list. The cells that I want to total up are named "StartTime" and "EndTime," and they come from "tblRelEventEmployee." The form shown here is "frmEvent."

Each job can have anywhere from 1 - 30 employees, and I need to be able to basically have a calculation that does EndTime-StartTime for each employee, and then add up the total for each to give me a grand total of hours worked for the job.

Any help would be greatly appreciated.

[ssanfu]

There is a function, DateDiff(), to get the hours between two date/times. I would put the function in the query for the form, then bind a text box control to that field (column) in the query. Do the times ever cross to the next day? If so, you will need the date component with the time. What about overtime?

[bradp1979]

Rarely do the times cross over to the next day, and I'm not concerned about overtime, since we handle that through QuickBooks. I just need the total.

Can you give me a little more step-by-step on how to make this all happen? I have an idea of what you're saying, but not 100% clear.

Thank you!

[June7]

Do you know how to construct fields in query with expression? Do that with DateDiff() function. Search Access Help or web for details on the function.

[bradp1979]

Unfortunately, I don't know how to do much of anything with a query.

[June7]

Type whatever you want for a field name and an expression in a cell of the Field row in query design grid.

Review http://www.fontstuff.com/access/acctut02.htm

[ssanfu]

In the form query, in a blank column, enter

WkHrs: DateDiff("h", StartTime, EndTime)

The function will return whole hours. (use Actual times, not scheduled)

On the form, add a text box and bind it to the "WkHrs" field (column).
Switch to normal view and see if the hours are as expected.

If you use

WkHrs: DateDiff("n", StartTime, EndTime)

DateDiff() will return minutes.

Examples:
StartTime = 6:15 pm
EndTime = 11:45 pm
----------------------------
WkHrs: DateDiff("h", StartTime, EndTime) = 5 hours

WkHrs: DateDiff("n", StartTime, EndTime) = 330 minutes

WkHrs: DateDiff("n", StartTime, EndTime)/60 = 5.5 hours



See HELP for the DateDiff function.

[bradp1979]

Ok, so going off of reading the link you provided, I did this. However, I'm not sure if I'm doing it right...

[June7]

That does not use DateDiff() function. The calc you show will return a value of days because that is the default unit when doing arithmetic with date/time fields. Must use DateDiff() to get other units (hours, years, etc).

Then you need a textbox in form footer to Sum the records. Expression in textbox: =Sum([yourfieldname]). Note that the calc will not update until new record is committed to table. Record is committed when moving to another record or close form or run code. Reports are best instrument for presenting aggregate calculations.

[bradp1979]

Ok, so I incorporated the code listed above by ssanfu, and I hit "run", and I got what looked to be correct. However, when I tried to put it on the footer of the form, but it's only giving me options to bind a field from a table to it, not a query.

[June7]

That is correct. The calculation must be included in the query that serves as the form's RecordSource. Then that calculated field will be available just like any other field.

[bradp1979]

Ok, how do I include it in the query that serves as the form's RecordSource?

[June7]

What is in the form's RecordSource - a table name or a query name or an SQL statement? If it's a table, will have to change to one of the other options. Build and save query object with all the fields (natural and calculated) needed by the form. Set that as the form's RecordSource.

This is basic Access functionality. Perhaps you should complete an introductory tutorial?

[bradp1979]

Actually, I figured it out. I added it to the query that is acting as the record source, and it gave me the option to bind it to the text box, so I did so. However, I'm still getting #Name? in the box. What am I missing?

And yes, I definitely need to complete an introductory tutorial. Just don't have time. Like I said, I'm learning it all on the fly. Luckily, I do it all on a test database that's saved to a folder on my desktop, so I won't mess up the actual database.

[June7]

What is the calculated field name? What is the expression in textbox ControlSource?