data type changes in a crosstab query

[Guest]

I have set up a crosstab query where the values are each the sum of a
particular amount in a table. The original data type for this amount in the
table is Currency, but the sums come out as Number in my query. I have the
equation listed as the Field, and Expression as the Total type. Why is the
data type different from the original, and how can I change it back?

 

[Allen Browne]

Open your crosstab query in SQL View (View menu, from query design).

On the first line, you will see something like:
TRANSFORM Sum(Amount) AS SumOfAmount

Typecast this with the CCur() to convert to currency.
CCur() can't handle nulls, so you also need to use Nz().

You will end up with the first line looking like this:
TRANSFORM CCur(Nz(Sum(Amount),0)) AS SumOfAmount

For other suggestions on how to get Access to understand calculated fields,
see:
http://allenbrowne.com/ser-45.html