Calculation in an Access Form

[Franzi]

Hello,
I've got 7 fields in a Form (monday to sunday) where you can put workinghours of an employee in. At the end I've got a "Total"-field where I want to calculate the sum of the 7 days. Where can I put a formula in to calculate this? Is it possible to do that in a form?
Thank you in advance!
Franzi

[Minion]

Hello,
I've got 7 fields in a Form (monday to sunday) where you can put workinghours of an employee in. At the end I've got a "Total"-field where I want to calculate the sum of the 7 days. Where can I put a formula in to calculate this? Is it possible to do that in a form?
Thank you in advance!
Franzi

Using a form you could put this in a number of locations. The three logical locations are:

• In the 'Got Focus' Event of the total box

• In the 'Current' event of the form itself

• Attached to the 'Click' event of a command button

Once you decide on the event the code is pretty simple. Providing the datatype is set to number for all it would look similar to:

with me
.TextTotal = .Text1 + .Text2 + .Text3 + .Text4 + .Text5 + .Text6 + .Text7
end with
 

Basically it will just add the values into the total box. This may or may not be bound to a column in the table itself.

Hope this helps.

- Minion -

[jaxjagfan]

If you have a a total textbox that is not bound (this just displays the total).
Its ControlSource would be =text1 + text2 + text3 ...

Is there a need to actually store the total since it is a calculated value? In the event the hours gets edited for a day then you don't have to worry about recalc total.

Make the default values zero (0). Access can't handle Nulls like Excel.

[Franzi]

If you have a a total textbox that is not bound (this just displays the total).
Its ControlSource would be =text1 + text2 + text3 ...

Is there a need to actually store the total since it is a calculated value? In the event the hours gets edited for a day then you don't have to worry about recalc total.

Make the default values zero (0). Access can't handle Nulls like Excel.

Thank you, I did that (=Monday+Tuesday....). now Access doesnt calculate the sum but just puts the numbers in a row like Monday is 12, tuesaday is 3 and Access puts in total: 123 instead of 15. Do you know how that comes?

[jaxjagfan]

Thank you, I did that (=Monday+Tuesday....). now Access doesnt calculate the sum but just puts the numbers in a row like Monday is 12, tuesaday is 3 and Access puts in total: 123 instead of 15. Do you know how that comes?

Because it sees the values as text and is concatenating.

Since employee time can be in partial hours (8.5, 11.75, etc)

=Cdbl(Monday) + Cdbl(Tuesday) ....

This will convert the value entered to a double and then add.

It would help to set the format of each textbox to a numeric format with a set number of decimal places.