Equation for comparying three quantities

[molsen]

Hi All

I have three sources of data which all equate to a reconciliation of the same data e.g. the same record of a stock holding is held on three separate systems, which must all be the same.

I am trying to build a an equation in a query field, which compares each quantity:

Qty Diff: [Dealer Qty]-[Pulse Qty]-[SWFAL Qty].

If the quantities are all the same, then I want to return a Zero. If one or more of them are different, I want that difference to show. Any idea on a variance of the above equation that would do it?

Many thanks

 

[vbaInet]

If the quantities are all the same, then I want to return a Zero.

If all three are equal the following will equate to True.

Qty Same?: ([Dealer Qty] = [Pulse Qty]) AND ([Pulse Qty] = [SWFAL Qty])

From there, use an IIF() function to equate it to 0.

On the other hand, you can use two nested IIF() functions.

Qty Same?: IIF([Dealer Qty]=[Pulse Qty], IIF([Pulse Qty]=[SWFAL Qty], 0, "Different"), "Different")

If one or more of them are different, I want that difference to show.

How do you intend displaying the difference?

 

[molsen]

Thanks VB, the first bit of code works perfectly - It displays as a zero, or the actual difference if there is one. I tested it by changing one of the three amounts

 

[vbaInet]

Good to hear

The problem is if all three are different how do you display that?
The equation I gave should only return True or False.

 

[molsen]

"Different" comes up if all three quantities are not equal, so that will give someone the heads up to investigate. In any case, all three fields with the quantities are shown on the screen, so it should draw the eye. I will also write a separate query to return only differences, or <> 0, for one of the reconciliation team to deal with.

Thanks!

 

[vbaInet]

Oh ok. I thought you wanted to display the actual value that's different.

You're good to go then!