General discussion

Locked

Access 2000

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?

This conversation is currently closed to new comments.

8 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

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])

Collapse -

by Navjun In reply to

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.

Collapse -

by Navjun In reply to Access 2000

Point value changed by question poster.

Collapse -

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.

Collapse -

by Navjun In reply to

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

Collapse -

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%

Collapse -

by Navjun In reply to

THank you .

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

Thanks

Collapse -

by Navjun In reply to Access 2000

This question was closed by the author

Back to Software Forum
8 total posts (Page 1 of 1)  

Related Discussions

Related Forums