How do I create a difference formula in a query?

Question

I want to create a column in my query that show the difference between two numbers; without generating the negative numbers by setting up a simple subtracting. The ex. below is what I want for the Difference Column.

                        Planned Lead Time      Actual Delivery          Difference

Part Number 1              15                           10                           5

Part Number 2              20                           20                           0

Part Number 3              30                           35                           5

Answer 1

Hi There,

Here's an expression that should accomplish what you need:

Difference: IIf(([planned]-[actual])>0,([planned]-[actual]),([planned]-[actual])*-1)

HTH,

Kathy

Answer 2

kayte wrote:

> I want to create a column in my query that show the difference between two numbers; without generating the

> negative numbers by setting up a simple subtracting. The ex. below is what I want for the Difference Column.

>

> Planned Lead Time Actual Delivery Difference

> Part Number 1 15 10 5

> Part Number 2 20 20 0

> Part Number 3 30 35 5

Difference: Abs(Planned-Actual)

--

Peter Doering [MVP Access]

Answer 3

Kathy S - Microsoft Support wrote:

> Good call Peter. I always forget about the ABS function..

Hi Kathy, I try to avoid IIF in queries wherever I can. I would even claim

there is no need for IIF in SQL, and that I can find a faster workaround.

Prove me wrong ... <g>

--

Peter Doering [MVP Access]

Answer 4

Good call Peter. I always forget about the ABS function..