Number data type showing negative (-) numbers

[Hek]

Hey all

On my access DB i have noticed that the "on order" values for one of my number data types is providing negative values, even the number itself isnt correct if i were to simply take the - sign away.

Attached is a screenshot of where the issue is presenting itself.

If anyone has any ideas, it would be greatly appreciated.

Regards, Rhys.

EDIT: Very new to Access, keep that in mind

 

[June7]

Interesting that all the non-negatives are 0. This is a calculated value? What is the calculation? Post query statement. If you want to provide db for analysis, follow instructions at bottom of my post.

 

[Hek]

Hey there

it is a number data type, not calculated.
Not sure why this is happening, any thoughts?

 

[June7]

No idea. Would have to review db.

 

[Hek]

how would i make a backup DB and wipe all the confidential info off to post here?

 

[June7]

Just copy the db as you would any file. Then open tables and delete records. Or run UPDATE queries to selectively empty fields.

 

[arnelgp]

You can obtain the OnOrder quantity by summing all the non-received orders.

not always the case.
https://bizfluent.com/how-6551577-calculate-reorder-quantity.html
https://www.inflowinventory.com/blog/reorder-point-formula-safety-stock/
http://www.accountingexplanation.com/reorder_level.htm

 

[Cronk]

In the original post, was that a screen shot of the table? It might be that Stock on Order is treated as negative usage, depending on system design. How do the numbers appear on form(s) relating to ordered stock? Creating a query based on the table with a negative in front of the field would display positive numbers.

 

[arnelgp]

it's obvious, the program has a calculation of its own.
accounting standard I supposed.

 

[Hek]

Private Sub Command34_Click()
If [Quantity] <= [Stock] Then
Me.Stock = Me.Stock - Me.Quantity
Me.Picking_ID = 4
Me.Fabrication_ID = 4
Me.Finishing_ID = 4
Me.Pressure_Testing_ID = 4
Me.Packing_ID = 0
Me.On_Order = Me.On_Order + Me.Quantity
Me.Status_ID = 0
Me.InventoryID = 2
'Inventory is good on this part'

ElseIf [Category] = "Catch Can" Then
Me.Picking_ID = 4
Me.Fabrication_ID = 4
Me.Finishing_ID = 4
Me.Pressure_Testing_ID = 4
Me.Packing_ID = 0
Me.On_Order = Me.On_Order + Me.Quantity
Me.Status_ID = 0
Me.InventoryID = 2

ElseIf [Quantity] > [Stock] Then

Me.TempNumber = Me.TempNumber + Me.Stock
Me.StockTemp = Me.StockTemp + Me.TempNumber
Me.TempNumber = Me.TempNumber - Me.Quantity
Me.TempNumber = Me.TempNumber * (-1)
Me.Quantity = 0
Me.Quantity = Me.Quantity + Me.TempNumber
Me.TempNumber = Me.TempNumber + Me.On_Order
Me.On_Order = Me.TempNumber
Me.Stock = 0
Me.Picking_ID = 1
Me.Status_ID = 0
Me.InventoryID = 2

End If
Me.TempNumber = 0
DoCmd.GoToRecord , , acNewRec
DoCmd.OpenForm "Master Form"
End Sub

Here is the code that i think is responsible for the issue, anyone have any thoughts?

 

[June7]

Saving aggregate data should be rare and only with strong justification. Review http://allenbrowne.com/AppInventory.html

 

[arnelgp]

the calculation will result in positive On Order.
maybe somehow somebody mess with the db and directly manipulate/enter data on the table directly and not by form.
you should rectify the table by going through all transactions and manually compute the totals.