If/then stmt in a module, please help!

[Danielle]

Hi, I need a module that will probably need to use an if/then stmt, but I am not sure how to use it. I have a formula that does a calculation for my report, but I need it to perform different calculations based on a field.

I need it to say:

if [Employee Type] = "Contractor" then [Sum Of BILLABLE HRS3]/([Sum Of EXP HRS ALL3]-[Sum Of GA07 OTHER NON PAID3])

otherwise do this: [Sum Of BILLABLE HRS3]/[Sum Of ACTUAL HRS3]

These calculations give a percentage as a value.

I would apprceciate any help you could give. Everyone is waiting on my report, but I can't get the formula to work right! Thanks so much.

 

[jaydlewis]

You can use IIf in an expression in the control of the report; or assuming your report is recordsourced to a query, use the IIf function there.

If you would like help with the function, post back what you have so far and include table and query and control names.

 

[Danielle]

This is what I have so far in the control source for the control, but the syntax is wrong:

=IIf[RESOURCE TYPE]="EMPLOYEE",[Sum Of BILLABLE HRS]/[Sum Of EXP HRS ALL],[Sum Of BILLABLE HRS]/[Sum of ACTUAL HRS]-[Sum Of GA07 OTHER NON PAID]

There are only 2 Resource Types-EMPLOYEE and CONTRACTOR. And those are the exact calcuations I want it to perform. RESOURCE TYPE is a field in the report. The "Sum Of" fields are controls which are in the report only, summing up the fields "EXP HRS ALL, BILLABLE HRS, ACTUAL HRS, and GA07 OTHER NON PAID".

Since this if stmt is using controls which are in the report only, the if stmt will have to be in the expression of the control I am trying to format and not the query.

Thanks for your help!