Using VBA for complex calculation

[Jsha]

Okay, I have been looking for awhile on these forums to find my answer and I have found a few, but nothing on this particular subject. Maybe its because my skill level is to low.

I have a database tracking hours, type of work, and money. (brief description)

Okay, in the database some multipliers have been setup, 2 to be exact. And now I need certain hours to be multiplied by 1 multiplier and the rest by the second multiplier.

in my report I have the calculation below to get the total money billed.

=IIf([Combo61]="15",[REGHOURS]*[EMP_RATE]*[EXPENSEMULTIPLIER],[REGHOURS]*[EMP_RATE]*[LABORMULTIPLIER])

The 15 represents the Type of work, this calulation work perfectly. I have another calculation in the subtotal area which is below.

=IIf([Combo61]="15",Sum([REGHOURS]*[EMP_RATE]*[EXPENSEMULTIPLIER]),Sum([REGHOURS]*[EMP_RATE]*[LABORMULTIPLIER]))

This also works perfectly, now the problem is when I am trying to get a grand total. I couldn't come up with another complex calculation to do it so I decided to research the coding of modules. So I wrote the code below for a module and now I am clueless on how to get it to work in my report.

Function GrandBilling()
Dim expenseVAR As Currency, laborVAR As Currency

If Combo61 = "15" Then

expenseVAR = ADSDETAIL_REGHOURS * EMP_RATE * FEE_EXPENSEMULTIPLIER

Else

laborVAR = ADSDETAIL_REGHOURS * EMP_RATE * FEE_LABORMULTIPLIER

End If

End Function

Now again I am completely new at coding modules and my VBA is extremely rusty. Everyone's help is greatly appreciated.

 

[traingamer]

You can just use the field name in the group and report footers. e.g. if your field described above is MoneyBilled, in your group footer, put =Sum(MoneyBilled) and the same thing in the report footer. Access will add it up for you.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[Jsha]

Sorry, I should have stated this before, the formulas are in an unbound text box. I wanted to put the formulas in the query so that they would be controls in the report, but Access kept telling me the formula was too complex.

 

[Remou]

There can be a problem summing calculated fields in some versions of Access reports:

http://support.microsoft.com/kb/207763

Or you may wish to consider calculating in a query and using that for the report.

 

[Jsha]

I was more looking for some help with regards to getting this function to work in my Report. Here come a very noob question, but how do I call the function I wrote above. I am trying to show the results in an unbound text box.