IIF statement not working

[twychopen22]

Expr1: (IIf([FixedExpenseOccurance]="Quarterly",[amount]/3,0))+(IIf([FixedExpenseOccurance]="SemiAnnually",[amount]/6,0))+(IIf([FixedExpenseOccurance]="Yearly",[amount]/12,0))

FixedExpenseOccurance is the the field and "*" is the values which get looked up from a table. I am trying to create an expense report. The query runs but no values are entered into the box?

For some reason it is asking for a parameter when I try t orun it. I don't have anything entered into the Criteria.

 

[raskew]

Hi -

Nested IIf() statements generally suck and should be avoided.

There's no place for '+' signs -- that won't work. You're not adding anything to anything.

Have to question the logic: If [amount] represents a yearly total then:
[amount]/4 would equal "Quarterly" -- not [amount]/3 (which would equate to 'every four months')
[amount]/2 would equal "SemiAnnually" -- not [amount]/6 (which would equate to 'every two months')
[amount] would equal "Annually" -- not [amount]/12 (which would equate to 'monthly')

Looking forward to hearng from you
Please explain.

Bob

 

[twychopen22]

Hi -

Nested IIf() statements generally suck and should be avoided.

There's no place for '+' signs -- that won't work. You're not adding anything to anything.

Have to question the logic: If [amount] represents a yearly total then:
[amount]/4 would equal "Quarterly" -- not [amount]/3 (which would equate to 'every four months')
[amount]/2 would equal "SemiAnnually" -- not [amount]/6 (which would equate to 'every two months')
[amount] would equal "Annually" -- not [amount]/12 (which would equate to 'monthly')

Well, the "represents a yearly total" is whatever is selected from a dropdown box and can be either monthly, quarterly, semiannually, or yearly. I am making reports for each. Any expense that is yearly is divided by 12 to make the monthly expense...do you see what I am doing? Every value has to be shown on each report weather it be monthly, quarterly, semiannually, or yearly, but if I am showing the monthly report, and the amount is only a semi yearly expense, I have to divide it to show what it would be monthly. Make sense? Anyways, so I am still stuck and can't get this formula to work...someone plz help, I am stuck!

 

[twychopen22]

Here is the problem again in a more detailed explanation and the problem that is occuring:

MonthlyExpenseAmount: (IIf(FixedExpenseOccurance)="Quarterly",(amount)/3,0)(IIf(FixedExpenseOccurance)="SemiAnnually",(amount)/6,0)(IIf(FixedExpenseOccurance)="Yearly",(amount)/12,0)

I was wondering if anybody could help me with this or if somebody could tell me how to write a query like this in SQL(which I don't know). I am making expense reports. A monthly, quarterly, semiannually, and annually. This is the code for the monthly. Now, whether or not the expense is monthly, it must show on the monthly report so- if it is semiannually, I divide it by 6 to show what it equals per month even though it only is paid every half year. Hope this makes sense. Next, the: "Quarterly" or "yearly" etc. is coming from a dropdown list. I have a table and the user selects what the occurance of the expense is, I am not sure what to do, if using the "" is the right thing to say "if the selection from the dropdown box = "quarterly"" Am I doing that the right way? Next, (amount) is the dollar value, so if I have a yearly expense that is $1200, the monthly expense should be $100. (amount) is the name of the field...I think I did that right but not sure. Last, the error it throws when I run this is that "the expression I have entered has a function containing the wrong number of arguments".

 

[twychopen22]

Please read the above one if you haven't it explains in detail the problem...anyways, I am going one by one to c if the IIf statement works. I figured out that I had parenthesis before the iif, can't do that. So now here it is:

IIf(fixedExpenseOccurance=Quaterly,amount/3,0)

Now that runs but it automatically places brackets (making it parameter) like this:

IIf([fixedExpenseOccurance]=[Quaterly],[amount]/3,0)

I don't want this in parameter. It should see the values...

 

[neileg]

Nope, you are mistaken. Field names should be enclosed in square brackets. If Access can't match a name enclosed in square brackets in the underlying data set, then it prompts for user input. Now because you have square brackets round [Quarterly] it wants user input. The proper syntax is:

IIf([fixedExpenseOccurance]="Quarterly",[amount]/3,0)

 

[twychopen22]

your right about the brackets...I realized that a little later after lookingat some examples...but it still ask for an imput. I have exactly what you have and it still asks for fixedexpenseoccurance

 

[neileg]

Make sure that you have correctly spelled the field name and that it exists in the underlying data. It doen't have to be in the query grid but it has to be in the table or query that your query is based.

 

[twychopen22]

OMG I went back and checked the spelling, I had it wrong after all, I was looking at the wrong stuff...I'm an idiot!!! thanks for your help. FixedExpneseOccurrance was spelled wrong...geez

 

[twychopen22]

I really appreciate your time and help.the /0 thing...I have a query that looks at all 4 (monthly yearly semi...etc) and figures it out so there will never be any zerost.

I figured it out, it was the dang spelling.

This is what I have now (for those interested) and it works. This is to show all expenses as monthly values i.e. if an expens is yearly divide it by 12 to show what it amounts to monthly.

(IIf(FixedExpenseOccurrance]="Quarterly",[amount]/3,0))
+(IIf(FixedExpenseOccurrance]="monthly",[amount]/1,0))
+(IIf(FixedExpenseOccurrance]="semiannually",[amount]/6,0))
+(IIf(FixedExpenseOccurrance]="annually",[amount]/12,0))

 

[twychopen22]

I have an expense say "window cleaning for $1200" that happense quarterly. (Every 3 months) I know what it is every 3 months but I want to see what it is worth every month. Therefore I divide by 3. 1200/3 = 400 a month.

This is a monthly report so if a new expense say "pens for 10 monthly". I want to see the monthly value so I divide by 1 to show itself. It works out with all 4 IIf statements

I also have a question that iam about to post and maybe u could help me with that one too, its in the general section