Conditional Calculation

[Samulayo]

I am producing a time recording database for a large company and have a front end installed on each computer for accessing the backend data. The front-end timesheet entry is basically a single record form with a continuous record subform linked via a replicationID produced on the first form.
I require two totals in the footer of the subform, one for invoiceable hours and one for non invoiceable hours.
The subform has 3 combo boxes for choosing the project that hours are being booked to and then text boxes for mon-sun; with the hidden field of invoiceable (dictated by the 3 rd combo box) and a foreign key.
I am unable to find a way to calculate totals for only the projects that are invoiceable and those that are not on continuous forms. Any insight would be greatly appreciated.

Regards

Samulayo

 

[bpurser]

Well, I think you are going to have to use either the Domain aggregate functions, or open a recordset in ADO to do the calc...if you are just summing a field, it's relatively easy, but once you want to do it conditionally, you have to either use DSum or open a recordset using the sum function with the appropriate criteria. Also, remember that you will need to save the current rec before doing that, or it won't be included.

HTH
Ben

 

[jimb0ne]

I'm not sure if I understand you correctly. Is there a reason you couldn't just cycle through using code and do the calculation that way?

 

[Samulayo]

Thank you for your replies, I have decided to proceed by having a the user enter the data to a temporary table and using the DSum() command on that, and when the user presses 'submit' the data will be written to the main table using an append query.

Regards

Samulayo

 

[bpurser]

Ok, make sure to have the db set to Compact on Close...it's going to get big if you are moving data back & forth between tables like that...

Ben