General discussion

  • Creator
    Topic
  • #2296892

    Access 2000

    Locked

    by navjun ·

    I need to setup query that will give me the variance between two fields. right now i’m useing ” Var: [quantity in system]/[physical counted] ” in the query filed. this gives me the percentage and sometimes its over 100 sometimes its under 100.
    so what I like to get done is that when it is over 100 I get a (+)(what ever percetgae over 100) and when its under 100 i get a (-)(what ever percengae under 100). for example if my var is 133% i like to see +33% or when its 65% I like to see -35%.

    Can you please help me do this?

All Comments

  • Author
    Replies
    • #2686401

      Reply To: Access 2000

      by dklippert ·

      In reply to Access 2000

      Use an IIF statement.Something like:

      Var: IIF([quantity in system]/[physical counted]>100, [quantity in system]/[physical counted]-100, 100-[quantity in system]/[physical counted])

      • #2684992

        Reply To: Access 2000

        by navjun ·

        In reply to Reply To: Access 2000

        Thanks , but sometimes I have some numbers that percentage is 4500 and it will not work by this way. let me know how else to do it. and I also need the Plus or minus sign in there.

    • #2684989

      Reply To: Access 2000

      by navjun ·

      In reply to Access 2000

      Point value changed by question poster.

    • #2684988

      Reply To: Access 2000

      by dklippert ·

      In reply to Access 2000

      The IIF statement was a suggestion.

      The form is:
      IIf(expr, truepart, falsepart)

      Create any formulas you need for the true/false parts.
      The results can be formatted as:

      +0.0;?0.0;0.0

      Display a plus (+) or minus (?) sign with positive or negative numbers; display 0.0 if the value is zero.

      • #2684937

        Reply To: Access 2000

        by navjun ·

        In reply to Reply To: Access 2000

        Can you elaborate more. I’m not so savvy at this.
        thank you

    • #2684926

      Reply To: Access 2000

      by dklippert ·

      In reply to Access 2000

      Let’s try this:
      VAR:([quantity in system]/[physical counted])-1

      Then Right-click the calculated field and choose Properties. On the Format line enter:
      +0.0%;-0.0%;0.0%

      • #2684758

        Reply To: Access 2000

        by navjun ·

        In reply to Reply To: Access 2000

        THank you .

        Much better . I did have to work it a little, but could not have done without your help.

        Thanks

    • #2684757

      Reply To: Access 2000

      by navjun ·

      In reply to Access 2000

      This question was closed by the author

Viewing 4 reply threads