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

MS Access Formula To Determine First Day of Following Month

Here is an easy way to determine the first day of the next month from today’s date using the DateAdd and DateSerial functions in MS Access. As an example, if today’s date is December 9, 2012, then this will result in January 1, 2013.

=DateSerial(Year(DateAdd("m",1,Now())),Month(DateAdd("m",1,Now())),1)

Query Expressions to Pivot Data in MS Access

Microsoft Access is a useful tool for quickly analyzing a data set, however, that data may not be structured in a convenient manner for producing a summarized view. As an example, let’s assume that a table exists with the columns “Item,” “Sale Date,” and “Price.”

ItemSale DatePrice
Widget 12/1/2012$10.00
Widget 23/1/2012$15.00

In our summary view, we want to see the data on an annual basis with each of the 12 months as individual columns as opposed to rows. Given the example table, we’ll need to pivot the data on the “Sale Date” column.

In Design View, twelve columns should be created to correspond to each of the 12 months in a year using the following expression. Only January through March are displayed for example purposes.

Jan: Sum(IIf(Month([Sale Date])=1,[Price],0))
Feb: Sum(IIf(Month([Sale Date])=2,[Price],0))
Mar: Sum(IIf(Month([Sale Date])=3,[Price],0))

Using these expressions, the result set from the query is structured in the following format:

ItemJanFebMarch
Widget 1$0.00$10.00$0.00
Widget 2$0.00$0.00$15.00