Search code examples
vbams-accessdecimalmodulus

VBA drops the decimal point when multiplying decimal with whole number

I have a userform that retrieves price amount from an Access table. Prices can be decimals. To compute the total prices of a line, I have to multiply it by the quantity.

A typical example is:

  • Price = 10.45
  • Quantity = 1

Logically, what I want is Tot = 10.45*1 = 10.45. But VBA gives me 1045 without the decimal point.

I have scrolled through the forum and seen some suggestions (converting to decimals, using modulus, declaring double, single, float,...) but the result does not change.

The code that I am using goes like this:

Me.lblPU = (rs!price - (rs!price  * (5 / 100)))
Me.lblPriceTot = (CDec(Me.lblPU) * CDec(Me.Qty))

The field lblPU is giving the correct number: 10.45 But the field lblPriceTot is giving 1045 as a result.

I didn't declare any variable regarding this fields because they are actually retrieved from Access and therefore are not encoded by a user.

Thanks in advance for any help!

Solution

  • Writing this in comments would be a mess. Instead of parsing a string to a decimal, directly use the decimal value:

    Me.lblPU = (rs!price - (rs!price  * (5 / 100)))
Me.lblPriceTot = ((rs!price - (rs!price  * (5 / 100))) * CDec(Me.Qty))

    Or:

    dim price as decimal = (rs!price - (rs!price  * (5 / 100)))
Me.lblPU = price
Me.lblPriceTot = (price * CDec(Me.Qty))