Absolute value

[Guest]

Hi, I have two tables that I am comparing, B4Inventory and AfterInventory. I want to get the difference in stock and value. I can't seem to check if the b4inv number is higher to either add or subtract to get the correct total, same with the dollars. I also need to sort on a calculated field!!
HELP HELP HELP
THANKS
MArc

 

[Steve Schapel]

I suggest some more information about the tables and the data, and some
examples, would help potential helpers to understand what you are trying
to do.

 

[Guest]

I, I figured that some more detail would be needed... So here we go :-

I have two tables one called BeforeInv and AfterInv, each contain the same layout, as follows
[ItemNumber] [Description] [PlannerCode] [OnHand] [ExtMats(Currency)] [FlrStk] [ExtMatif(Currency)

I have created a make table query combine BeforeInv and AfterInv into one record per item numbe
The new table is as follows
[ItemNumber] [Description] [PlannerCode] [B4InvOnHand] [AfterInvOnHand] [B4InvExtMats(Currency)] [AFterInvExtMats(Currency)] [B4InvFlrStk] [AfterInvFlrStk] [B4InvExtMatif(Currency)] [AfterInvExtMatif(Currency)

I want to run a query against the new table, which will display the following
[ItemNumber] [Description] [B4InvOnHand] [AfterInvOnHand] [(new) StkDiffQuanity] [(new)StkValue] [B4InvFlrStk] [AfterInvFlrStk] [(new) FlrDiffQuanity] [(new)FlrValue] [(new)TotalItemDiffQty] [(new)TotalItemDiffValue

I am having trouble with the expression builder, I can add the beforeInv and afterInv but For OnHand and FlrStk I need the absolute value difference: ex -1 and 5 should be 6 not 4. The Value should be for 6 not 4 also. The item totals should be handled the same way. I also want to Sort Descending on the ItemTotal Value.

Any and all help would be greatly appreciated

Thanks
Mar

 

[Guest]

I reread my last post and wanted to clear up some questions that I think will come up

"I am having trouble with the expression builder, I can add the beforeInv and afterInv" I can do simple addition or subtraction, Ex. StkDiffQty:[B4InvOnHand]+ -[AfterInvOnHand]
I just do not know what I need to do for absolute value difference...
ex -1 and 5 should be 6 not 4.

"The Value should be for 6 not 4 also. " The Currency value should be for the total difference in StkQty

"The item totals should be handled the same way. " This way if the Stk is a positive and FlrStk is negative I will get the absolute value difference of those in the Total Field

Thanks Again
Mar

 

[Steve Schapel]

Marc,

Maybe you can use the Abs() function, for example...
StkDiffQty: Abs([B4InvOnHand])+Abs([AfterInvOnHand])