Currency Field will not show currency format.

[Robeen]

Hi,

I'm working with Access 2010.

I have two tables [Week1, Week2] that each have a Currency field - Week1_Balance & Week2_Balance.

I have a Week1 unmatched query, a Week2 unmatched query and a Week1 -> Week2 matched query that I made into a Union All query.

My report runs off the Union Query. It will not allow me to designate the two Balance fields as Currency. Under the Format Tab - the $ option is grayed out.

I tried entering a Currency format in the Properties Sheet ($#,###.00) - but that doesn't work either. It just displays as a number.



I should add that I am using the nz functon on the Balance fields and setting the valueifnull to 0 [zero].

Any ideas why I cannot set these fields as currency in my report?

I'm wondering if somewhere along the way [in one of the queries] I have changed the data-type unwittingly to something else.



Thanks!

Robeen

[Robeen]

Please cancel this request for help. I have been able to solve the problem.

What I did was use a CCur() on each of the Balance fields in the union query. Then - when I opened the Report in design View - I was able to assign the Currency format '$' to the fields.

However, I have still NOT figured out what action in my query chain converted what started out as Currency fields in the tables -> into text fields.

I WAS able to determine that they had been converted into text when I exported the report to Excel where the cells with these values had little red markers in the corners and when I hovered over them I got a message saying they were text.

So - problem solved . . . but question lingers!

So: and I guess!

Robeen

[pbaldy]

I've seen the Nz() function do that, even with 0 specified as the value if Null. You can experiment by taking it out and seeing if the values return to numbers (you can tell by how they align when you run the query; left = text, right = number).