Reputation: 29
I am trying to update an inventory stock as follows:
[InventoryStock] = ([CurrentInventoryStock]-[QuantityOrdered])
Note that QuantityOrdered
can be decimal, something like: 5.2 or can be full number, something like 8.
InventoryStock
column is set to Number (double)QuantityOrdered
column is also set to Number (double)When QuantityOrdered
is a full number, like 5, it works perfectly; but when QuantityOrdered
is a decimal number, like 7.5 , then I receive:
Run-time error 3144 (syntax error in update statement)
...highlighting the update code that I wrote in VBA.
If Not IsNull(Me.QuantityOrdered) Then
CurrentDb.Execute " UPDATE Inventory SET InventoryStock = InventoryStock - " & Nz(Me.QuantityOrdered.Value, 0) & ""
End If
As described above, my goal is to deducted the quantity ordered (whether decimal or full number) from the Inventory Stock value.
Can someone help me out?
Upvotes: 0
Views: 214
Reputation: 3455
Regarding your problem:
Using implicit string conversion will use a decimal separator following your country/language settings, for example a (,
), which causes your issue.
So you should explicitely use Str()
to convert the numeric value to a string to get a dot (.
) as separator.
Two more small remarks:
NZ(…)
is not necessary because you already check that before (If Not IsNull(Me.QuantityOrdered) Then
).& ""
) is not necessary too.If Not IsNull(Me.QuantityOrdered) Then
CurrentDb.Execute "UPDATE Inventory SET InventoryStock = InventoryStock - " & Str(Me.QuantityOrdered.Value)
End If
Upvotes: 2