Rounddown on iif statement

  • Thread starter Thread starter hoachen
  • Start date Start date
H

hoachen

Is there a way to rounddown the final calculation to 0.25 and have 2 decimals?
IIf([Net]=0 And [Disc]=1,(Format(
  • )*0.50*1.0))
 
I'm not sure I have quite enough information to help.

Round WHAT down ... and everything rounds to ".25"?

If you want to "have 2 decimals", you can use format() to control that.

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
IIf([Net]=0 And [Disc]=1,(Format(
  • )*0.50*1.0))
    This calculation will set the 2 decimals

    Let said the net price = 0 and the list price = $5.8 so, the calculation
    would be 5.8*.50*1.0 = $2.9 and round it down to a quarter should be $2.50

    Does this make sense?

    Jeff Boyce said:
    I'm not sure I have quite enough information to help.

    Round WHAT down ... and everything rounds to ".25"?

    If you want to "have 2 decimals", you can use format() to control that.

    More info, please...

    Regards

    Jeff Boyce
    Microsoft Access MVP

    --
    Disclaimer: This author may have received products and services mentioned
    in this post. Mention and/or description of a product or service herein
    does not constitute endorsement thereof.

    Any code or pseudocode included in this post is offered "as is", with no
    guarantee as to suitability.

    You can thank the FTC of the USA for making this disclaimer
    possible/necessary.

    hoachen said:
    Is there a way to rounddown the final calculation to 0.25 and have 2
    decimals?
    IIf([Net]=0 And [Disc]=1,(Format(
    • )*0.50*1.0))
    Click to expand...



    • .
    Click to expand...
 
Take another look at Acces HELP for the correct syntax for the Format()
function. I don't see what format you are telling Access to use.

"Round it down to a quarter" ... I'm still not getting it. If I have $2.90,
and round it down to the nearest quarter, I get $2.75.

More info, please!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

hoachen said:
IIf([Net]=0 And [Disc]=1,(Format(
  • )*0.50*1.0))
    This calculation will set the 2 decimals

    Let said the net price = 0 and the list price = $5.8 so, the calculation
    would be 5.8*.50*1.0 = $2.9 and round it down to a quarter should be $2.50

    Does this make sense?

    Jeff Boyce said:
    I'm not sure I have quite enough information to help.

    Round WHAT down ... and everything rounds to ".25"?

    If you want to "have 2 decimals", you can use format() to control that.

    More info, please...

    Regards

    Jeff Boyce
    Microsoft Access MVP

    --
    Disclaimer: This author may have received products and services mentioned
    in this post. Mention and/or description of a product or service herein
    does not constitute endorsement thereof.

    Any code or pseudocode included in this post is offered "as is", with no
    guarantee as to suitability.

    You can thank the FTC of the USA for making this disclaimer
    possible/necessary.

    hoachen said:
    Is there a way to rounddown the final calculation to 0.25 and have 2
    decimals?
    IIf([Net]=0 And [Disc]=1,(Format(
    • )*0.50*1.0))
    Click to expand...



    • .
    Click to expand...
Click to expand...
 
Ya, my bad, it should be $2.75. On this (Format(
  • )*0.50*1.0)), it does
    not tell what format?

    Jeff Boyce said:
    Take another look at Acces HELP for the correct syntax for the Format()
    function. I don't see what format you are telling Access to use.

    "Round it down to a quarter" ... I'm still not getting it. If I have $2.90,
    and round it down to the nearest quarter, I get $2.75.

    More info, please!

    Regards

    Jeff Boyce
    Microsoft Access MVP

    --
    Disclaimer: This author may have received products and services mentioned
    in this post. Mention and/or description of a product or service herein
    does not constitute endorsement thereof.

    Any code or pseudocode included in this post is offered "as is", with no
    guarantee as to suitability.

    You can thank the FTC of the USA for making this disclaimer
    possible/necessary.

    hoachen said:
    IIf([Net]=0 And [Disc]=1,(Format(
    • )*0.50*1.0))
      This calculation will set the 2 decimals

      Let said the net price = 0 and the list price = $5.8 so, the calculation
      would be 5.8*.50*1.0 = $2.9 and round it down to a quarter should be $2.50

      Does this make sense?

      Jeff Boyce said:
      I'm not sure I have quite enough information to help.

      Round WHAT down ... and everything rounds to ".25"?

      If you want to "have 2 decimals", you can use format() to control that.

      More info, please...

      Regards

      Jeff Boyce
      Microsoft Access MVP

      --
      Disclaimer: This author may have received products and services mentioned
      in this post. Mention and/or description of a product or service herein
      does not constitute endorsement thereof.

      Any code or pseudocode included in this post is offered "as is", with no
      guarantee as to suitability.

      You can thank the FTC of the USA for making this disclaimer
      possible/necessary.

      Is there a way to rounddown the final calculation to 0.25 and have 2
      decimals?
      IIf([Net]=0 And [Disc]=1,(Format(
      • )*0.50*1.0))


        .
      Click to expand...
    Click to expand...



      • .
    Click to expand...
 
There is no reason to multiply by 1 as that has no effect on the value. 1 *
anything is an identity and yields the starting value. To round down to the
nearest quarter, you would need an expression like the following:
Int(X * 4)/4
Or if you want to round towards zero then use
Fix(X*4)/4

IIf([Net]=0 And [Disc]=1,Fix(
  • *0.50 * 4)/4)

    Int and Fix will give you the same values on positive numbers. On negative
    numbers they work slightly differently. Using Int will round towards negative
    infinity while Fix will round towards zero.
    -1.76 will round to -2 with Fix and will round to -1.75 with Int.

    Hope this helps.

    John Spencer
    Access MVP 2002-2005, 2007-2010
    The Hilltop Institute
    University of Maryland Baltimore County
 
Hoachen -

To round down to the quarter, multiply by 4, take the integer portion, and
divide by 4. You don't need the Format piece, as you are returning the
actual value rounded down to the quarter:

IIf([Net]=0 And [Disc]=1,(int(
  • *0.50*1.0*4)/4))

    I don't see an 'else' clause in your IIF statement, so mine above doesn't
    have one either.

    --
    Daryl S


    hoachen said:
    Ya, my bad, it should be $2.75. On this (Format(
    • )*0.50*1.0)), it does
      not tell what format?

      Jeff Boyce said:
      Take another look at Acces HELP for the correct syntax for the Format()
      function. I don't see what format you are telling Access to use.

      "Round it down to a quarter" ... I'm still not getting it. If I have $2.90,
      and round it down to the nearest quarter, I get $2.75.

      More info, please!

      Regards

      Jeff Boyce
      Microsoft Access MVP

      --
      Disclaimer: This author may have received products and services mentioned
      in this post. Mention and/or description of a product or service herein
      does not constitute endorsement thereof.

      Any code or pseudocode included in this post is offered "as is", with no
      guarantee as to suitability.

      You can thank the FTC of the USA for making this disclaimer
      possible/necessary.

      hoachen said:
      IIf([Net]=0 And [Disc]=1,(Format(
      • )*0.50*1.0))
        This calculation will set the 2 decimals

        Let said the net price = 0 and the list price = $5.8 so, the calculation
        would be 5.8*.50*1.0 = $2.9 and round it down to a quarter should be $2.50

        Does this make sense?

        :

        I'm not sure I have quite enough information to help.

        Round WHAT down ... and everything rounds to ".25"?

        If you want to "have 2 decimals", you can use format() to control that.

        More info, please...

        Regards

        Jeff Boyce
        Microsoft Access MVP

        --
        Disclaimer: This author may have received products and services mentioned
        in this post. Mention and/or description of a product or service herein
        does not constitute endorsement thereof.

        Any code or pseudocode included in this post is offered "as is", with no
        guarantee as to suitability.

        You can thank the FTC of the USA for making this disclaimer
        possible/necessary.

        Is there a way to rounddown the final calculation to 0.25 and have 2
        decimals?
        IIf([Net]=0 And [Disc]=1,(Format(
        • )*0.50*1.0))


          .
      Click to expand...



        • .
      Click to expand...
    Click to expand...
 
Back
Top