Need help to Simplify a Complex Calculation on a Form

Mar 28, 2014
Reaction score
I would like to get your expert advice on simplifying calculation on form. I have a form to do search on my item [ItemCode] and display result with its sales records and stock quantity. For my stock calculation, I used following formula:
Stock quantity at Store = Item Purchase to Store + Item transfer to Store (from Warehouse) – Item Sold from Store – Item transfer from Store (to Warehouse)

The code I use on my form is
=Nz(DSum("[Qty]","tblPurchaseOrderDetails","[SentTo] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
+Nz(DSum("[Qty]","tblStockTransaction","[InvAction] = 'Xfer2Store' AND [Location] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
-Nz(DSum("[Qty]","tblStockTransaction","[InvAction] = 'Xfer2Warehouse' AND [Location] = 'Warehouse' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)
-Nz(DSum("[Qty]","tblOrderDetails","[SentFrom] = 'Store' AND [ItemCode] = " & [Forms]![frmStockSearch]![ID]),0)

[frmStockSearch] : the form where I did my search.
tblPurchaseOrderDetails : table for Purchase records which has [SentTo] option for sending item to Store or Warehouse.
tblStockTransaction : table for item movement form / to Store / Warehouse which has [InvAction] as inventory action (transfer to Store or transfer to Warehouse) and [Location] as Destination of Item (Store or Warehouse).
tblOrderDetails : table for sales records which has [SentFrom] option for sending item out from Store or Warehouse.
[ItemCode] : Item Code where I enter on my search form.
[ID] : the ID of the item.

This calculation works fine, however you can see the repeating of “ & [Forms]![frmStockSearch]![ID]),0 ” on every parameter. If I have extra warehouse, then this code will be come even longer. Is there any way to simplify this calculation?

Thank you for your help. I greatly appreciate that.



Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question