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.
