IIf Syntax in a report textbox

[tammyt]

I need help with a report in Access 2000! How would you do this?

If [checkbox 1] is checked and [checkbox 2] is not checked then [Total] textbox should have same value as [field 1].
If [checkbox 2] is checked and [checkbox 1] is not checked then [Total] textbox should have same value as [field 2].
If [checkbox 1] and [checkbox 2] are both checked then [Total] textbox should have value of ([field 1] + [field 2]).

Report Example:

Field 1 Field 2 CheckBox 1 CheckBox 2 Total
5 2 X 5
3 3 X 3
4 5 X X 9

I'm pretty sure I need an IIf statement in the Total textbox record source, but I not sure of the syntax.

Can anyone help me?

 

[jfgambit]

Try this:

=IIf([check1]=Yes And [check2]=No,[textbox]=[field1],IIf([check1]=No And [check2]=Yes,[textbox]=[field2],[textbox] = [field1]+[field2])))

HTH

 

[tammyt]

I tried you code and Access accepted the syntax. However, when I ran the report the Total Units textbox show "#Error". I don't get it. I check the format of all the fields. Field 1 and field 2 are both number fields with field size double and format standard. The Total Units textbox is formatted as standard. Am I missing something?

 

[ListO]

Too much equality.

The above solution is essentially correct, but it has too many equal signs. Only one equal-sign is required at the beginning, the value which follows the conditional commas will be returned. To use the same code as jfgambit gave, you'd make it:

=IIf([check1]=Yes 
And [check2]=No,[field1],IIf([check1]=No 
And [check2]=Yes,[field2],[field1]+[field2]))

It should work. At least that's what I always tell myself.

 

[tammyt]

Yeeeeeee Haaaaaaa!

It worked! As soon as I saw your code I knew what I had done wrong. I didn't need to put the Total Units field in the equation! Thank you! Thank you! Thank you!

 

[jfgambit]

Oops....silly me...
Now why would you need to tell the textbox that it equals itself...

Must have been that 4th cup of coffee I had this morning...

 

[Pat Hartman]

There is actually a 4th possibilty which is that neither is checked.