# Thread: IIf Statement Help

1. Registered User
Join Date
Mar 2004
Posts
361

## Unanswered: IIf Statement Help

Can anyone tell me why I get an error with this function. What I'm trying to say is this.

If MSPR is greater than 986310 Cap then multiply 986310 with 986310 Cap else if MSRP is less than msrp cap then multiply 986310 with MSRP.

Thanks for the help.

MVP: IIf([MSRP]>"986310 Cap","986310 Cap"*"986310",IIf([MSRP]<"986310 Cap","986310"*[MSRP]))

2. Registered User
Join Date
Jul 2003
Posts
81

## Re: IIf Statement Help

Please explain me what is the CAP;

3. Registered User
Join Date
Mar 2004
Posts
361
986310 cap is the name of the field I want to use in the calculation.

4. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
What are '986310 Cap' and '986310' are they fields in a table? If so, try:

IIf([MSRP]>[986310 Cap],[986310 Cap]*[986310],IIf([MSRP]<[986310 Cap],[986310]*[MSRP]))

5. Registered User
Join Date
Mar 2004
Posts
361
They are the names of calculations. They are not on a table. Would your sug still work?

6. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
yes.

izy

oh, and i guess you want the answer too

"986310 Cap" is a piece of text not a field value. ditto "986310". multiplying something by a piece of text is ...unusual.

try:

MVP: IIf([MSRP]>[986310 Cap],[986310 Cap]*[986310],IIf([MSRP]<[986310 Cap],[986310]*[MSRP]))

i don't know what you are trying to achieve: you seem to be planning to return NULL if [MSRP]=[986310 Cap]. Was that intentional?

it goes:
iif(logicalExpression, trueReturn, falseReturn)

izy

7. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
oops: so many answers at the same time. izy

8. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
where are [99999 CAP] and his friends?
on a form, in a function, in a dimmed var, or ???

izy

9. Registered User
Join Date
Mar 2004
Posts
361
MSRP is never equal to 986310 cap. So that is really not a problem. I still get an #error when I run it. I can get it to work if I just multiply msrp by 986310 but I need that extra option of 986310 cap time 986310.

10. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
...and i reread your first post: you are not looking for a null.

so it's simpler:

MVP: IIf([MSRP]>[986310 Cap],[986310 Cap]*[986310],[986310]*[MSRP])

voila: no null

..but i still don't know where 99999 CAP lives: if it is a calculation living in the query, you are probably OK, but do yourself a favour and dont put spaces in the names of things.

izy

11. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
simultaneous again (at least at my typing speed).
i'll leave it to cook for 10 minutes and come back

izy

12. Registered User
Join Date
Mar 2004
Posts
361
MVP: IIf([MSRP]>1,[MSRP]*"986310")

I got this function to return a value. I tried to replace 1 with 986310 Cap but i returned an error. Poo on this.

13. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
WHERE or WHATis this damn 99999CAP thing?

izy

14. Registered User
Join Date
Mar 2004
Posts
361
986310 Cap is the name of the field just like 986310. Both are names to different calculations. Would it matter if I took out the cap part?

15. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
please, a few more taps wont kill your keyboard.

your expression
MVP: blah blah
looks like it is in Access' query grid.

you say
986310 Cap is the name of the field just like 986310
but where: in the table, in the query, or????

you said earlier that these guys are "calculations". keep in mind that Access cannot perform some calculations-on-calculations in one step. if the 98XXXX fields are generated in query qry1

make a new qry2 looking at qry1 and do the next step of the calculation there.

izy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•