Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    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. #2
    Join Date
    Jul 2003
    Posts
    81

    Question Re: IIf Statement Help

    Please explain me what is the CAP;

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

  4. #4
    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. #5
    Join Date
    Mar 2004
    Posts
    361
    They are the names of calculations. They are not on a table. Would your sug still work?

  6. #6
    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
    currently using SS 2008R2

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    oops: so many answers at the same time. izy
    currently using SS 2008R2

  8. #8
    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
    currently using SS 2008R2

  9. #9
    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. #10
    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
    currently using SS 2008R2

  11. #11
    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
    currently using SS 2008R2

  12. #12
    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. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    WHERE or WHATis this damn 99999CAP thing?


    izy
    currently using SS 2008R2

  14. #14
    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. #15
    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
    currently using SS 2008R2

Posting Permissions

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