formular (1 Viewer)

[rainbows]

i am trying to create a formular that allows me to add the allocated stock into it but struggling , at this time it looks at what the order quantity is minus what's in stock . what's on order and and tells me the quantity to order. IE DIFFQTY1 in the sceenshot below tells me i am minus 1on 2 items

I am now trying to include what in allocated for example diffyqty now tells me i need a minus 6 and a minus 1 the problem i am having is the criteria <0 when i put this in the diffqty column i get this error

this is what is in the diffqty1 diffqty1: Round([StockQty]+[OnOrderQty]+[PendingQty]-Sum([Order Details]!OrderQty*[Product Detail]!ProductQty),4)


thanks
steve

 

[LarryE]

i am trying to create a formular that allows me to add the allocated stock into it but struggling , at this time it looks at what the order quantity is minus what's in stock . what's on order and and tells me the quantity to order. IE DIFFQTY1 in the sceenshot below tells me i am minus 1on 2 items

I am now trying to include what in allocated for example diffyqty now tells me i need a minus 6 and a minus 1 the problem i am having is the criteria <0 when i put this in the diffqty column i get this error


View attachment 102875



View attachment 102877
this is what is in the diffqty1 diffqty1: Round([StockQty]+[OnOrderQty]+[PendingQty]-Sum([Order Details]!OrderQty*[Product Detail]!ProductQty),4)


thanks
steve

Try:
IIf(IsNull([SumOfqty allocated]),...etc.

 

[rainbows]

i tried that but got this error


diffqty: IIf(isnull([SumOfqty allocated],[diffqty1],Round([StockQty]+[OnOrderQty]+[PendingQty]-[stock allocation Query]![SumOfqty allocated]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]),4))

 

[rainbows]

this managed to do it but i think it could be made smaller ?


diffqty: IIf([SumOfqty allocated] Is Null,Round([StockQty]+[OnOrderQty]+[PendingQty]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]),4),Round([StockQty]+[OnOrderQty]+[PendingQty]-[stock allocation Query]![SumOfqty allocated]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]),4))

 

[LarryE]

i tried that but got this error


diffqty: IIf(isnull([SumOfqty allocated],[diffqty1],Round([StockQty]+[OnOrderQty]+[PendingQty]-[stock allocation Query]![SumOfqty allocated]-Sum([Order Details]![OrderQty]*[Product Detail]![ProductQty]),4))




View attachment 102878

That is because you missed the ending paraenthisis after SumOfqty allocated. Try again exactly like I typed it. IsNull[[SumOfqty allocated])