I currently have a query that calculates each Department's "Daily Average Charge as follows ([SumOfCharges] / 365). Here's the problem; 2 of the departments are new to the system and don't have 12 months of charges - so we want to divide their total charges by the number of days they've been in the system. One Department has been in the system for 4 months and the other - 2 months. When there was only one new department, I used a simple "If, Then" expression in the query to obtain the average daily charge. But what do I do now that I have 2 exceptions - and therefore 3 scenarios. The calculation needs to take place at the beginning of each month.
Here is the expression currently in my query; =IIf([Div_ID] = "Surg18",[Charges]/123, [Charges]/365).
I'm assuming I'll need a public function, but I don't know how to refer to fields within a table in a VBA function....... I'm at a loss...
Fran
Here is the expression currently in my query; =IIf([Div_ID] = "Surg18",[Charges]/123, [Charges]/365).
I'm assuming I'll need a public function, but I don't know how to refer to fields within a table in a VBA function....... I'm at a loss...
Fran