Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

multiple IIF statements

Discussion in 'Information Technology' started by Levans digital, Jul 28, 2006.

  1. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Help me
    I have two separate IIF Statements.
    (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)

    Is there any possibility of combining these 2 statements. I wont mind
    accomplishing it using VB.
    These expresiions are within a query currently which will be based on a form
    later on.

    Thanks
     
  2. Loading...


  3. Wayne-in-Manchester

    Wayne-in-Manchester
    Expand Collapse
    Guest

    IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))



    "Levans digital" wrote:

    > Help me
    > I have two separate IIF Statements.
    > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    >
    > Is there any possibility of combining these 2 statements. I wont mind
    > accomplishing it using VB.
    > These expresiions are within a query currently which will be based on a form
    > later on.
    >
    > Thanks
     
  4. Levans digital

    Levans digital
    Expand Collapse
    Guest

    Hi Wayne,

    When I tried your expression in a query it say that I am missing a closing
    Prenthesis, backet orvertical bar. I tried using my discretion adding in
    closing.. and ended up getting message: that a comma is the problem
    Note I tried this expression
    IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    but the results are only for the first "IIF' Statement the second "IIF" does
    not occur.
    What do u think?

    "Wayne-in-Manchester" wrote:

    > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    >
    >
    >
    > "Levans digital" wrote:
    >
    > > Help me
    > > I have two separate IIF Statements.
    > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > >
    > > Is there any possibility of combining these 2 statements. I wont mind
    > > accomplishing it using VB.
    > > These expresiions are within a query currently which will be based on a form
    > > later on.
    > >
    > > Thanks
     
  5. xRoachx

    xRoachx
    Expand Collapse
    Guest

    Hi Levans -- I'm not sure these IIf statements can be combined b/c I don't
    see a direct connection between the two. One statement is comparing Pounds to
    Cubic Feet and the other is comparing Kilograms to Cubic Meters...what's the
    correlation? What happens if both statements are true?

    "Levans digital" wrote:

    > Hi Wayne,
    >
    > When I tried your expression in a query it say that I am missing a closing
    > Prenthesis, backet orvertical bar. I tried using my discretion adding in
    > closing.. and ended up getting message: that a comma is the problem
    > Note I tried this expression
    > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > but the results are only for the first "IIF' Statement the second "IIF" does
    > not occur.
    > What do u think?
    >
    > "Wayne-in-Manchester" wrote:
    >
    > > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > >
    > >
    > >
    > > "Levans digital" wrote:
    > >
    > > > Help me
    > > > I have two separate IIF Statements.
    > > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > > >
    > > > Is there any possibility of combining these 2 statements. I wont mind
    > > > accomplishing it using VB.
    > > > These expresiions are within a query currently which will be based on a form
    > > > later on.
    > > >
    > > > Thanks
     
  6. Wayne-in-Manchester

    Wayne-in-Manchester
    Expand Collapse
    Guest

    Access will take up to 7 nested if's so it should be possible to work out
    most If's.
    Can you post an example of the data you're trying to work on (you can send
    made up data if you want).

    IIf([Pounds]>=([Cubicfeet]*50),[pounds],(IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)))








    "Levans digital" wrote:

    > Hi Wayne,
    >
    > When I tried your expression in a query it say that I am missing a closing
    > Prenthesis, backet orvertical bar. I tried using my discretion adding in
    > closing.. and ended up getting message: that a comma is the problem
    > Note I tried this expression
    > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > but the results are only for the first "IIF' Statement the second "IIF" does
    > not occur.
    > What do u think?
    >
    > "Wayne-in-Manchester" wrote:
    >
    > > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > >
    > >
    > >
    > > "Levans digital" wrote:
    > >
    > > > Help me
    > > > I have two separate IIF Statements.
    > > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > > >
    > > > Is there any possibility of combining these 2 statements. I wont mind
    > > > accomplishing it using VB.
    > > > These expresiions are within a query currently which will be based on a form
    > > > later on.
    > > >
    > > > Thanks
     
  7. Wayne-in-Manchester

    Wayne-in-Manchester
    Expand Collapse
    Guest

    I'm off home now - end of a busy day.

    Anyway

    I just loaded to field names you gave into a small practice d base and have
    the follow notes for you.

    Yes you can combine the If's - BUT
    If the 1st if is TRUE (the Pounds field is equall to or more than the
    Cubicfeet field times 50) you will get the data in your Pounds field
    If it's not (ie, it's less than) AND your Kilograms field is more than or
    equal to the cubicMeters field times 800.92,then the answer will be your
    kilograms field times 2.2046.
    BUT
    If the answer to both is NOT true you will get 0
    Don't forget that if the 1st if IS true you will get the answer in your
    Pounds field

    Confusing the hell out of me - but if you know what you're looking for it
    must be ok.

    Hope this helps (off for a cup of tea now as my head is hurting with that one)




    "Levans digital" wrote:

    > Hi Wayne,
    >
    > When I tried your expression in a query it say that I am missing a closing
    > Prenthesis, backet orvertical bar. I tried using my discretion adding in
    > closing.. and ended up getting message: that a comma is the problem
    > Note I tried this expression
    > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > but the results are only for the first "IIF' Statement the second "IIF" does
    > not occur.
    > What do u think?
    >
    > "Wayne-in-Manchester" wrote:
    >
    > > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > >
    > >
    > >
    > > "Levans digital" wrote:
    > >
    > > > Help me
    > > > I have two separate IIF Statements.
    > > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > > >
    > > > Is there any possibility of combining these 2 statements. I wont mind
    > > > accomplishing it using VB.
    > > > These expresiions are within a query currently which will be based on a form
    > > > later on.
    > > >
    > > > Thanks
     
  8. Levans digital

    Levans digital
    Expand Collapse
    Guest

    I have 8 fields in a query
    Pounds (Number field)
    Cubic Feet (Number field)
    Kilograms (Number field)
    Cubic Meters(Number field)
    CalLBS: (Calculated)
    CalCf: (Calculated)
    CalLBS2: (Calculated)
    CalCf2: (Calculated)


    I have existing fields with statements for the 4 calculated field
    (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds]) (Note: Final Answer is in
    pounds)
    (2) CalCf: IIf([CubicFeet]*50>[Pounds],[CubicFeet]) (Note final Answer in
    Cubic Feet)
    (3) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046) (Note
    that
    “*2.2046†bring the kilograms value to pounds which in my mind
    relates it to the first
    Calculated field which is also pounds. My goal is to eliminate the
    “CalLBS2†and use only
    “CalLBS†field the expressions for both field into one which in my
    previous post looks like
    THIS: CalLBS:
    IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))


    It was asked in the post what if both are true. Well at start out data will
    only be imputed in either the pounds and cubic feet fields or Kilograms and
    cubic meters field. The calculated field should only accept one condition.

    Guys I hope you understand my situation here is sample data
    Pounds cubicfeet kilograms cubicmeteters CalLBS CalLBS2
    100 1 100
    2000 1 4409.2

    My Goal
    Pounds cubicfeet kilograms cubicmeteters CalLBS CalLBS2
    100 1 100
    2000 1 4409.2

    Note that 2000 kilograms *2.2046 = 4409.2 pounds

    Mein its Lengthy but its my best.


    "Wayne-in-Manchester" wrote:

    > I'm off home now - end of a busy day.
    >
    > Anyway
    >
    > I just loaded to field names you gave into a small practice d base and have
    > the follow notes for you.
    >
    > Yes you can combine the If's - BUT
    > If the 1st if is TRUE (the Pounds field is equall to or more than the
    > Cubicfeet field times 50) you will get the data in your Pounds field
    > If it's not (ie, it's less than) AND your Kilograms field is more than or
    > equal to the cubicMeters field times 800.92,then the answer will be your
    > kilograms field times 2.2046.
    > BUT
    > If the answer to both is NOT true you will get 0
    > Don't forget that if the 1st if IS true you will get the answer in your
    > Pounds field
    >
    > Confusing the hell out of me - but if you know what you're looking for it
    > must be ok.
    >
    > Hope this helps (off for a cup of tea now as my head is hurting with that one)
    >
    >
    >
    >
    > "Levans digital" wrote:
    >
    > > Hi Wayne,
    > >
    > > When I tried your expression in a query it say that I am missing a closing
    > > Prenthesis, backet orvertical bar. I tried using my discretion adding in
    > > closing.. and ended up getting message: that a comma is the problem
    > > Note I tried this expression
    > > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > > but the results are only for the first "IIF' Statement the second "IIF" does
    > > not occur.
    > > What do u think?
    > >
    > > "Wayne-in-Manchester" wrote:
    > >
    > > > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > > > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > > >
    > > >
    > > >
    > > > "Levans digital" wrote:
    > > >
    > > > > Help me
    > > > > I have two separate IIF Statements.
    > > > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > > > >
    > > > > Is there any possibility of combining these 2 statements. I wont mind
    > > > > accomplishing it using VB.
    > > > > These expresiions are within a query currently which will be based on a form
    > > > > later on.
    > > > >
    > > > > Thanks
     
  9. xRoachx

    xRoachx
    Expand Collapse
    Guest

    "Levans digital" wrote:

    > I have 8 fields in a query
    > Pounds (Number field)
    > Cubic Feet (Number field)
    > Kilograms (Number field)
    > Cubic Meters(Number field)
    > CalLBS: (Calculated)
    > CalCf: (Calculated)
    > CalLBS2: (Calculated)
    > CalCf2: (Calculated)
    >
    >
    > I have existing fields with statements for the 4 calculated field
    > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds]) (Note: Final Answer is in
    > pounds)
    > (2) CalCf: IIf([CubicFeet]*50>[Pounds],[CubicFeet]) (Note final Answer in
    > Cubic Feet)
    > (3) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046) (Note
    > that
    > “*2.2046†bring the kilograms value to pounds which in my mind
    > relates it to the first
    > Calculated field which is also pounds. My goal is to eliminate the
    > “CalLBS2†and use only
    > “CalLBS†field the expressions for both field into one which in my
    > previous post looks like
    > THIS: CalLBS:
    > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    >
    >
    > It was asked in the post what if both are true. Well at start out data will
    > only be imputed in either the pounds and cubic feet fields or Kilograms and
    > cubic meters field. The calculated field should only accept one condition.
    >
    > Guys I hope you understand my situation here is sample data
    > Pounds cubicfeet kilograms cubicmeteters CalLBS CalLBS2
    > 100 1 100
    > 2000 1 4409.2
    >
    > My Goal
    > Pounds cubicfeet kilograms cubicmeteters CalLBS CalLBS2
    > 100 1 100
    > 2000 1 4409.2
    >
    > Note that 2000 kilograms *2.2046 = 4409.2 pounds
    >
    > Mein its Lengthy but its my best.
    >
    >
    > "Wayne-in-Manchester" wrote:
    >
    > > I'm off home now - end of a busy day.
    > >
    > > Anyway
    > >
    > > I just loaded to field names you gave into a small practice d base and have
    > > the follow notes for you.
    > >
    > > Yes you can combine the If's - BUT
    > > If the 1st if is TRUE (the Pounds field is equall to or more than the
    > > Cubicfeet field times 50) you will get the data in your Pounds field
    > > If it's not (ie, it's less than) AND your Kilograms field is more than or
    > > equal to the cubicMeters field times 800.92,then the answer will be your
    > > kilograms field times 2.2046.
    > > BUT
    > > If the answer to both is NOT true you will get 0
    > > Don't forget that if the 1st if IS true you will get the answer in your
    > > Pounds field
    > >
    > > Confusing the hell out of me - but if you know what you're looking for it
    > > must be ok.
    > >
    > > Hope this helps (off for a cup of tea now as my head is hurting with that one)
    > >
    > >
    > >
    > >
    > > "Levans digital" wrote:
    > >
    > > > Hi Wayne,
    > > >
    > > > When I tried your expression in a query it say that I am missing a closing
    > > > Prenthesis, backet orvertical bar. I tried using my discretion adding in
    > > > closing.. and ended up getting message: that a comma is the problem
    > > > Note I tried this expression
    > > > IIf([Pounds]>=[Cubicfeet]*50,[pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > > > but the results are only for the first "IIF' Statement the second "IIF" does
    > > > not occur.
    > > > What do u think?
    > > >
    > > > "Wayne-in-Manchester" wrote:
    > > >
    > > > > IIf (([Pounds]>=[Cubicfeet]*50,[pounds],
    > > > > (IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046))
    > > > >
    > > > >
    > > > >
    > > > > "Levans digital" wrote:
    > > > >
    > > > > > Help me
    > > > > > I have two separate IIF Statements.
    > > > > > (1) CalLBS: IIf([Pounds]>=[Cubicfeet]*50,[pounds])
    > > > > > (2) CalLBS2: IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046)
    > > > > >
    > > > > > Is there any possibility of combining these 2 statements. I wont mind
    > > > > > accomplishing it using VB.
    > > > > > These expresiions are within a query currently which will be based on a form
    > > > > > later on.
    > > > > >
    > > > > > Thanks
     
  10. Wayne-in-Manchester

    Wayne-in-Manchester
    Expand Collapse
    Guest

    CalLBS:
    IIf([Pounds]>=[Cubicfeet]*50,[Pounds],IIf([Kilograms]>=[CubicMeters]*800.92,[kilograms]*2.2046,0))

    Or
    Assuming that details are only entered in to each form once this will give
    you the pound equivalent. I think I have understood the converstion formulas
    but if not you can simply insert the correct numbers into the formula to
    convert each field into pounds. You may then use the pounds within any
    reports etc that you want (or convert this back using a simple equation

    CalLBS:
    Nz([Pound])+Nz([CubicFeet]*50)+Nz([Kilograms]*2.2046)+Nz([CubicMeters]*800.92)

    Hope this helps
     

Share This Page