Report Total Error

[darreno]

I have a table with two columns; [Number] and [Amount]. On the report, I have 3 fields; [Number], [Amount1] & [Amount2].

[Amount1] is =IIf([Number] Like "[A-G]",[Amount],"")
[Amount2] is =IIf([Number] Like "[H-Z]",[Amount],"")

The details are coming up correctly but I am having an error message “Enter Parameter Value” for the total for Amount1 and Amount2.

[Total Amount1] is =Sum([Amount1])
[Total Amount2] is =Sum([Amount2])

The totals are in the Report Footer.

What did I do wrong? Thanks for any advise.

 

[pbaldy]

You can't sum a calculated control; you have to repeat the calculation within the Sum().

 

[darreno]

I am still having trouble with the following:

=Sum(IIf([Number] Like "[A-G]",[Amount],""))

 

[pbaldy]

That should work. Make sure you don't have a control named "number". The wizard creates controls with names the same as fields, but it can screw things like this up.

 

[darreno]

I changed the control for number to number1 and have a "Data Type Mismatch in criteria expression."

 

[pbaldy]

Can you post a sample db?

 

[darreno]

Here is the db.

 

[pbaldy]

Sorry, the problem should have dawned on me to start with. Try this:

=Sum(IIf([Number] Like "[A-G]",[Amount],0))

 

[darreno]

It's working now. Thank you very much. So, it's the quotation that is causing the problem?

 

[pbaldy]

Yes, it was trying to sum a non-numeric value, hence the error. I should have noticed it earlier.