By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,611 Members | 1,464 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,611 IT Pros & Developers. It's quick & easy.

IIFStatement #Name

100+
P: 418
My report has a text box called txtFedAmount. Here I am trying to get the total amount of money for federal revenue.

Expand|Select|Wrap|Line Numbers
  1. FieldName      DataType        Description
  2. RevDescr         text              Federal / Local etc
  3. Budget            Currency        dollar amount
I am trying to add the federal amount of revenue portion in txtFedAmount

Expand|Select|Wrap|Line Numbers
  1. =IIf(([RevDescr]) Like “Federal”,Sum([Budget]),"")
But I keep getting #Name? What am I doing wrong?
Jun 10 '09 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,287
"Like" is used in SQL; it's not a valid operator in an IIf statement.
Jun 10 '09 #2

100+
P: 418
ChipR:

Thanks. I found a solution to this problem. In the event some other novice like myself gets into this rut, here is the solution:

Expand|Select|Wrap|Line Numbers
  1. =Sum(Abs([RevDescr]="Federal") * [Budget])
Jun 10 '09 #3

NeoPa
Expert Mod 15k+
P: 31,712
A bit late to the show here but let me see if I can clear up some misconceptions.

IIf() is a function that can be used from VBA OR SQL. Like is not usable within VBA code, however it is fine within SQL. The formula for a control would be SQL based, therefore Like is fine (in this case).

Abs() returns the absolute (unsigned) value of the passed parameter. The Boolean value TRUE is numerically equivalent to -1, and FALSE to 0, so this will work when the value is exactly equal to "Federal". This is also true for your original usage of Like. Like is misused here. = was the comparator required in these circumstances.

Lastly, to the actual, original problem :
Expand|Select|Wrap|Line Numbers
  1. “Federal” <> "Federal"
Notice the quotes. The one on the left is not using quote characters that are even recognised by Access as such. Hence the error reported.

A more conventional solution (simply fixing the original code) might be :
Expand|Select|Wrap|Line Numbers
  1. =Sum(IIf([RevDescr]='Federal',[Budget],0)
I think I prefer your code though.
Jun 15 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.