The issue occurs when using VBA code to return a currency value to be displayed on the report and it appears to be randomly multiplied by 10 or 100. This is a known issue (Problem Report 59380) and this post should help explain what the problem is and provide an alternative solution.
When working with variables in VBA and wanting to display them on a report, the usual method is to:
- Create a calculated field of the matching data type and set it to a blank or zero constant value.
- Place the calculated field onto the report layout in the desired location.
- Add the calculated field to Visual Basic.
- Use VBA code to set the value of the field from your code.
This method works fine for most data types. However, it does not work correctly when working with currency values. The problem is that the decimal point is not being correctly interpreted.
123.00 shows as 123.00
123.40 shows as 1,234.00
123.45 shows as 12,345.00
This gives the appearance of randomly multiplying the value by 10 or 100. It is not really random, it just depends on how many significant (non-zero) numbers there are after the decimal point.
The solution is to use a string calculated field on the report layout instead. This field can be formatted to align it to right and populated from VBA using the FormatCurrency() or FormatNumber() functions.
This method is mentioned on the Using VBA with Report Writer post as a note.
To demonstrate the issue I have created a custom report which returns the above three currency values using both methods. Below is the code used and the output produced.
Value in VBA Currency Field Formatted String
123.00 $123.00 $123.00
123.40 $1,234.00 $123.40
123.45 $12,345.00 $123.45
NOTE: The package with the VBA Currency Test custom report is attached to the bottom of this post.
Hope this helps.
09-Aug-2010: See follow-up article: Currency values returned by VBA to a report are multiplied by 10 or 100 with Multicurrency.