Calculatung a total in a form

[bpifer23]

The question is this......I have a form that has numerous time fields. At the end of the form I need to get the total for all of these time fields. For the life of me I can't figure this out. The kicker is that not all of the fields have information in them. The reason I need this total field is that off of this form I am creating a report which takes all the data from this form and creating a report that does calculations. That comes to my second problem. If I make these fields from the original table a number field (which allows me to put the default value to zero and therefore giving me the Total time) the wonderful division by zero rule comes about. Then I tried to make all of these fields a text (choosing the option to make a null value valid) and this didn't allow me to get the total time field.

Any help would be great......I am stuck and frustrated....Tahnks a head of times.

Brian

 

[M8KWR]

The only way i know how to do this would be to have a subform inside your form.

This owuld display your data, then in this form you would have field in the footer contained the calculation your require, then you would pass this to the main form, and have it updating when you update the data inside the sub form.

Hope this helps.

 

[willir]

Brian

Either in the footer or header for the subform, or on the main form, use the DSum function. Instead of trying to add numbers on the form / subform, you will grab the sum from the actual table...

For the ControlSource, enter something like...
[tt]
=Nz(DSum ("[YourSumField]", "YourTable", "[YourForeignKey] = " & [ForeignKeyOnForm]), 0)
[/tt]

If you add a Refresh button to the form / subform, it will calculate the new sum if you just created new detail entries.

The Nz() function wll accommodate null values.

If your foreign key is a string character, you will have to encapusulate the foreign key on the form with double quotes.

[tt]
=Nz(DSum ("[YourSumField]", "YourTable", "[YourForeignKey] = '" & [ForeignKeyOnForm]) & "'", 0)
[/tt]
...or variation thereof

Richard

 

[dikkydangel]

Dear anyone

I've created a table, along with a form that reflects the fields in the table. My question relates to calculations. When I create a calculation based on two fields on the form(bound)time_in and Time_out .....=[Time_out]-[Time_in]....and put it into the properties of Total_time as a "default value", the result does not appear on the form or it's corresponding field in the table. However when I change the "control" property to the above calculation the result comes out right on the form but does not appear in the table field. Is it impossible to calculate in a form and have the table updated with the results?

thanks
Ken

 

[willir]

Ken

You probably could have posted a new thread for this problem considering the original post is a year old.

Several things here...
- Per the rules for Normalization, it is not usually a good idea to store calculated values
- There are several conidtions where you will want to calculate / recalculate the value
- Are your "times" using the time data type? Or are you using numbers ?
- Use DateAdd and DateDiff functions when working with time and date values.

Discussions in the following thread are worth reviewing...
Add data to a table from an equation, how?

You will probably have to use code instead of default values and such.

Use a subroutine or function to calculate / recalculate the time difference. Call the function or subroutine where required including OnCurrent event procedure.

Use the Nz function if working with numbers or IsDate function if working with time / date fields. Use Refresh to update values to the table.

Richard