Iff Statement Problem 1

[yaksdogs]

A little info here. I have a report that will show duplicate values in but here is the kicker those need to be shown but not added into the total. I have created a check box labeled (included_value). This report also has quite a bit of divide by zero. So in the subtotal field have

=Sum(IIf([assessment_level]=0," ",[assessed_value]/[_assessment_level]))

It is working, well it looks like it.

Now how to do alter the code so that duplicate value is not added in if the check box is check.

How do I write the statement?

Any help is welcome.

 

[PaulBricker]

Wouldn't you follow the same logic for your check box, something like this.

=Sum(IIf(CheckBox = 0,0,FieldValue))

Paul

 

[yaksdogs]

How would you do it if it were to affect more than one field?

I have been trying to do it this way?

=Sum([ASSESSED_VALUE]),iif([included_value]=0,0,[tot_av])

There are about 4 fields that this affects on one report.

 

[PaulBricker]

Maybe something like:

=Sum(IIf([included_value]=0,0[tot_av] + [Assessed_Value])

First we test the value of included_value, then if it tests true we add tot_av and Assessed_Value together, and then we Sum those values up. If I'm missing something let me know.

Paul

 

[yaksdogs]

Ok I tried it now I am getting an error

Undefined function in "IIf" statement.

What I am I doing wrong here?

 

[PaulBricker]

Where are you running the expression. Some built in function don't run in queries, but IIF isn't one of them. Maybe a reference problem, but that would surprise me also. To view the reference libraries availble to your database, you need to open a module, new or existing, and on the menu bar go to Tools....References. All the available libraries have a check in the box on the left. They should all be clustered at the top of the list. If you can post which ones they are, I will check them against my list.

Paul

 

[yaksdogs]

These are the items checked:

Visual Basic for applicaitons
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.1 Library

Does this help?

 

[PaulBricker]

You can try finding these two libraries (the libraries are listed alphabetically so they would be under "M")

Microsoft DAO 3.6 Object Library
Mocrosoft Visual Basic for Applications Extensibility 3.5

Let me know how it goes.

Paul

 

[yaksdogs]

I just wanted to let you know that did it and the calculations seem to be working.

Thanks for much for your help.

Have a great holiday.

 

[PaulBricker]

That great. Glad it helped.

Regards,

Paul