IIF Statement

[chavez_sea]

Dear All,




I am trying to write an IIf statement to feature the formula below. The formula is in my report

The some of amount = 0, and therefor the result of my formula below is returning a result #Num!

=Sum([Sum total of records])/Sum([SumOfAmount])
anks

=IIf([SumOfAmount]=0,0,[Sum total of records]/[SumOfAmount])

Can someone please tell me what is wrong with the above statement.

Th

[John_G]

When [SumOfAmount] = 0 , the iif does not prevent the expression [Sum total of records]/[SumOfAmount] from being evaluated, which results in division by 0.

Try something like this:

IIf([SumOfAmount]=0,0,[Sum total of records]/iif([SumOfAmount]=0,1,[SumOfAmount])

This will give you 0 if SumOfAmount=0, otherwise it gives you the result of [Sum total of records]/[SumOfAmount], which is what you are looking for.

[chavez_sea]

Hi John,
Thanks for your reply.
I tried your suggestion, and it fixed one, and created another. See attached report in pdf of the original report, and the new report using the formula you sent.
Any other suggestion. I am also attaching a screen shot of the formulas.

Thanks

[CJ_London]

are [Sum total of records] and [SumOfAmount] actually 0 or are they nulls? - look at your recordsource query - do you see 0's there? - make sure there is no formatting properties specified for the fields to show 0 when null

[chavez_sea]

Hi John,
I got that one working, but I have another similar formula I need to make using

where I need Sum([SumOfRecords1])/Avg([Amount]) using the IIf =0 It tells me I have too many arguments. Again this formula is in the report

Thanks

[chavez_sea]

Hi Ajax,
The values are 0. I got that one working, but using the formula John sent I cant fix the other one I just posted. RE
where I need Sum([SumOfRecords1])/Avg([Amount]) using the IIf =0 It tells me I have too many arguments. This is a formula in my report.

I need this for work in the morning, I appreciate any assistance.

Thanks

[CJ_London]

what is the full formula? - too many arguments says what the problem is

iif(something is true, then do this, otherwise do that) - 3 arguments

you seem to have spaces in your field names, you are probably missing a square bracket

[chavez_sea]

=IIf([SumOfAmount]=0,0,[Sum total of records]/IIf([SumOfAmount]=0,1,[SumOfAmount]))

[chavez_sea]

Please see attached Document. I hope this explains my problem.

Thanks

[chavez_sea]

Sorry I thought I had the previous on e fixed, but I got confused. Indeed in the previous set of reports, the ones with the zero for all fields got fixed when I used the formula from John, but it made an error calculation for 1.1 heading and details 1.1.1 . I do hope I can get some assistance.

Thanks very much.

[CJ_London]

your form images are unreadable - you need a higher resolution, perhaps zoom in on the affected controls.

Also show the recordsource to your report and the rows where you are getting your error

Also, do the relevant tables use lookup, calculated or multivalue fields?

#num usually means nulls - so also try

=IIf(nz([SumOfAmount],0)=0 or nz([Sum total of records])=0,0,nz([Sum total of records])/nz([SumOfAmount]))

[chavez_sea]

Can I please send a small version of the database?



Thanks

[CJ_London]

yes - zip it and attach the zipped file

[chavez_sea]

Thank you. It is attached. The reports that I have the problem with is the

Summary table of budget consumption by Budget Heading

Detail table of Expenditure by Budget Heading

Kind regards

[CJ_London]

sorry - horribly complex - I can't find either report you are referring to. Please remove all unnecessary queries, forms and reports so we can see what we are looking at - and remove the autoexec!