VBA FormatNumber (1 Viewer)

[bigshop]

RLAmount = FormatNumber((CDbl(Nz(txtPurPrice)) * CDbl(Nz(txtRLFeePercent)) / 100), 2)
Debug.Print "RLAmount = " & RLAmount

Result:
RLAmount = 10

Im trying to get this to return a result of 10.00

 

[June7]

Your expression should work, however, not sure you need CDbl() nor all those parens.

FormatNumber(Nz(txtPurPrice, 0) * Nz(txtRLFeePercent, 0) / 100, 2)
or
Format(Nz(txtPurPrice, 0) * Nz(txtRLFeePercent, 0) / 100, "0.00")

In both cases the output is a string, not an actual number value.

What is RLAmount - a field or a textbox (name of both)? Why save calculated value to table? Field will not retain trailing decimal zeros unless it is a text type. Is textbox set to display decimals?

 

[The_Doc_Man]

Although your expression produces the wrong value, the fact that it returns a non-blank, non-zero value at all is significant. It implies that:

1. CDBL( NZ( txtPurPrice ) ) is not 0
2. CDBL( NZ( txtRLFeePercent ) ) is not 0

Which is to say, neither txtPurPrice nor txtRLFeePercent are blank.

Just be aware that if NZ( string ) is executed, the default value isn't 0, it is "" (the empty string). You only get a default of 0 for NZ( number ). Therefore, I would be a bit nervous about a defaulted NZ 2nd argument when datatype changes are occurring in the line.

The second argument of FORMATNUMBER is the number of places so that is right. Therefore, the question has to be why that expression for your RLAmount doesn't get formatted. If you set a breakpoint on the line, you might be able to look at the intermediate values. OR you could do a divide and conquer by computing (as DOUBLE variables) the two CDBL( NZ( ) ) expressions sepately, then combine them in a distinct step to see what your intermediates look like.

The only other explanation I get from throwing darts at my dart board seems to be a sneaky datatype-change. It probably won't make a big difference, but first, I would change the "/100" to "/100.0" - just as a "belt-and-suspenders" method to assure you do not have a forced type-change because of the integer- mode 100 in that expression. I don't believe it would do this, but it is a cheap test to perform.

 

[gemma-the-husky]

What data type is RLAmount?

What values produce a result of 10?
Try values that should give you a result like 10.125, and see what happens?