Currency Formatted Field Addition in MS Access Report

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.

Microsoft Access Monthly Spend Table Structure
Microsoft Access Monthly Spend Table Structure

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

Microsoft Access Monthly Spend Table Data
Microsoft Access Monthly Spend Table Data

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.

Microsoft Access Monthly Spend Query
Microsoft Access Monthly Spend Query
Microsoft Access Monthly Spend Query Results Concatenated
Microsoft Access Monthly Spend Query Results Concatenated

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.

Microsoft Access Monthly Spend Query Using CCur() Function
Microsoft Access Monthly Spend Query Using CCur() Function
Microsoft Access Monthly Spend Query Results Using CCur() Function
Microsoft Access Monthly Spend Query Results Using CCur() Function