In Microsoft Access, attempting to perform a mathematical operation on currency formatted
Text fields results in concatenated text instead of executing the appropriate calculation. The data source used for the report stored amounts as formatted text instead of numbers which caused Access to import the fields as
Short Text. I realize I could have transformed the data into the correct numeric format during the extract, but it was a one-time exercise and using the
CCur() function described below proved to be a faster solution. For reference, the
CCur() function takes the parameter value and returns a currency value.
As an example, the table Monthly Spend Table has three
Short Text fields intended to contain currency formatted amounts.
To demonstrate the issue, the Monthly Spend Table is populated with the following sample data. The values in each of the monthly columns is a string representing a USD amount prefixed with a dollar sign
If a simple query is executed to add the three column together, the result is the concatenation of each of the fields to form the string $5.00$10.00$3.00 instead of the intended $18.00 value.
This may be the expected result, however, it isn’t what I intended to see returned. If the query is slightly modified to wrap each column in the
CCur() function, then the query returns the expected result of $18.00.