Number data type showing negative (-) numbers (1 Viewer)

Hek · Aug 27, 2019

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 · Aug 27, 2019

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 · Aug 27, 2019

Hey there

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

June7 · Aug 27, 2019

No idea. Would have to review db.

Hek · Aug 27, 2019

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

June7 · Aug 27, 2019

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

Pat Hartman · Aug 27, 2019

You cropped the first picture too tightly. Are you showing a Form or a table in DS view?

OnOrder might not be calculated in a query but it is surely calculated based on an order as it is placed. That is one of the no-no's of normal database design. Calculated values should not be stored. You can obtain the OnOrder quantity by summing all the non-received orders.

arnelgp · Aug 27, 2019

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 · Aug 27, 2019

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.

Pat Hartman · Aug 27, 2019

not always the case.

The quantity on order can be obtained by summing the open orders (i.e. Orders that have not yet been received)
The links you posted were for how to calculate how much to order and when to place the order.

arnelgp · Aug 27, 2019

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

Pat Hartman · Aug 27, 2019

I did mention back in # 7 that on order was calculated in some manner. But on order is not the same as figuring out how much to order and when. On order simply reports what is already in the pipeline. It may be part of calculating how much to order but they are not the same thing.

Hek · Aug 27, 2019

Code:

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 · Aug 28, 2019

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

arnelgp · Aug 28, 2019

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.

Number data type showing negative (-) numbers (1 Viewer)

Hek

Registered User.

Attachments

June7

AWF VIP

Hek

Registered User.

Attachments

June7

AWF VIP

Hek

Registered User.

June7

AWF VIP

Pat Hartman

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Cronk

Registered User.

Pat Hartman

Super Moderator

arnelgp

..forever waiting... waiting for jellybean!

Pat Hartman

Super Moderator

Hek

Registered User.

June7

AWF VIP

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread