Originally Posted by
efmbman
I am looking to set up a calculated field in a report.
I would recommend NOT using calculated fields (in a table - if this is what you mean). Do the calculation in a query.
Using Paul's formula, I set up a table (no spaces in field names) and created this query:
Code:
SELECT Table2.Site, Table2.ActiveClients, Table2.DirectFTEE, Table2.DetailedFTEE, Table2.ProvidedFTEE, Table2.VacantFTEE, [ActiveClients]/([DirectFTEE]+IIf([VacantFTEE]>0,[VacantFTEE],[DetailedFTEE])+[ProvidedFTEE]) AS Result
FROM Table2;
The results were:
Site |
ActiveClients |
DirectFTEE |
DetailedFTEE |
ProvidedFTEE |
VacantFTEE |
Result |
698 |
18 |
6.35 |
0 |
0.3 |
1.5 |
2.21 |
642 |
34 |
5 |
0 |
0 |
0 |
6.80 |
558 |
42 |
4.2 |
0 |
0 |
0 |
10.00 |
In the query I set the format to fixes and 2 decimals.