ezybusy
ezybusy

Reputation: 29

How to calculated decimal values in vba access?

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.

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

Answers (1)

AHeyne
AHeyne

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).
  • Appending just an empty string (& "") 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

Related Questions