Calcuated Query for Instock Field

[emjhay13]

Hi Everyone
I would like to ask something regarding the calculated query for Instock field in a Inventory System see details below are the sample format field of my Inventory Transaction Table

Date	TransactionType	Company name	ProducType	ItemDescription	Qty	UOm	Dim	Unit PRice	Total Amount	RefNO	Instock	Remarks

My Inventory are based on the preset of Microsoft Access Inventory Management.

I have only three table InventoryTransaction, ProductDetails and Transaction Type and One query table known as Extended InventoryTransaction

So my Problem was the Instock field, I need to calculate the actual stock of the Item.See details below coding the Instock field

Instock: IIf([InventoryTransaction].[TransactionType]=[”Incoming”],[InventoryTransaction]![Qty],-([InventoryTransaction]![Qty]))

Translation of the code
The Transaction Type has a two records only which is the Incoming and Outgoing,
Incoming item are for the addition stock of the Item while the Outgoing is to decrease the stock of the Item so the purpose of the Instock field is to calculate the update stock,

The Instock field located at the Extended InventoryTransaction Query,whenever I open the this query . I encounter the First error, which
is a dialog box open and looking for "incoming" to enter the paramater value.


I just hit ok to go inside the table.



I really need your help guys
I need to finish this Inventory database even though I creating it from the scratch and thank you very much for replying my message.

[pbaldy]

You get the parameter prompt because you've bracketed the value. Drop those:

"Incoming"

[emjhay13]

HI pbaldy even though i erase the bracket sign Incoming it still looking for Incoming parameter,

[pbaldy]

What is the SQL now?

[emjhay13]

Pbaldy i didt use any sql command anyway see the codes below

SELECT InventoryTransaction.*, ProductDetails.*, TransactionType.*, [UnitPrice]*[Qty] AS TotalAmount, IIf([InventoryTransaction].[TransactionType]=[”Incoming”],[InventoryTransaction]![Qty],-([InventoryTransaction]![Qty])) AS Instock
FROM TransactionType INNER JOIN (ProductDetails INNER JOIN InventoryTransaction ON ProductDetails.ProductID=InventoryTransaction.Item Description) ON TransactionType.TransactionID=InventoryTransaction .TransactionType;

[pbaldy]

That is SQL and you still have brackets around Incoming.

[emjhay13]

Pbaldy when i try to eliminate the bracket from the sql view i got a new error

[pbaldy]

What's the actual SQL now? I see some spaces in the SQL you posted before that are either out of place or in a field name that would require it to be bracketed.

[emjhay13]

I already posted the actual sql up to this comment, i dont know how to remove the bracket i tried everything and i got the same error

[pbaldy]

The brackets around Incoming appear to be fine now, but in the SQL you posted there is a space in "Item Description"; if the field name actually contains the space, it must be bracketed. There's also one here: "InventoryTransaction .TransactionType" which can't be there.