Currency Formatting in Color 1

[awl]

I have three fields in my report that I am having a problem in trying to format them in a “currency format with color”. The report source is a query using the Switch function. Currently the figures are displayed as 20 for $20.00 or just 23.25 for $23.25. I want the three different colors, one for each of the three fields as listed below. I have the Decimal Places set at 2. This is not a conditional formatting issue to my knowledge. Any help would be greatly appreciated. Thank you.

Opening Balance: $#,##0.00 [Blue]

Deposits: $#,##0.00 [Green]

Withdrawals: $#,##0.00 [Red]

 

[SteveR77]

In the properties of the fields in question set the format to be Currency.

For the color set the foreground color to the shade of the color you desire.


HTH,


Steve

 

[awl]

Yes, thank you for responding. For the 1st item, I had done this before, setting it to Currency, in which in the report textbox, I have to type in the word Currency. Something I did not mention along with the Switch function in the query, when the query is ran, the figures are right aligned in each of the 3 fields. If I use the $#,##0.00 in the format of the query, the figures then become right aligned; I can not get it to be currency format, plus as above, the 20 is displayed instead of $20.00 with the format mentioned.

And thanks for the 2nd item, my colors are showing as I want them.

Hopefully the 1st item can yet be addressed. Curtis...

 

[SteveR77]

Curtis,

Try wrapping your switch funtion with a CCur. It should take care of the alignment and the formatting issue for you.


So the fields would look like CCur(Switch(....Expresion))


HTH,


Steve

 

[awl]

Thank you. Your suggestion works, however one little bit remains. Now, where there should be Zeros $0.00; an #Error is displayed. Is there a way to have a Zero displayed? I have pasted the four fields in the query if this should help. The [Balance] field had always displayed the running balance formatted correctly. It was just the other 3 fields.

OpeningBalance: CCur(Switch([tblCHBK Transactions].[TRACTType]=1,[tblCHBK Transactions].[TRACTAmount]))

Deposits: CCur(Switch([tblCHBK Transactions].[TRACTType]=2,[tblCHBK Transactions].[TRACTAmount]))

Withdrawals: CCur(-(Switch([tblCHBK Transactions].[TRACTType]=3,[tblCHBK Transactions].[TRACTAmount])))

Balance: (SELECT SUM(t2.TRACTAmount) FROM [tblCHBK Transactions] AS t2 WHERE t2.TRACTID <= [tblCHBK Transactions].TRACTID)

Thank you. Curtis....

 

[SteveR77]

Curtis,

Try the following -

OpeningBalance: CCur(Nz(Switch([tblCHBK Transactions].[TRACTType]=1,[tblCHBK Transactions].[TRACTAmount])))

Deposits: CCur(Nz(Switch([tblCHBK Transactions].[TRACTType]=2,[tblCHBK Transactions].[TRACTAmount])))

Withdrawals: CCur(-(Nz(Switch([tblCHBK Transactions].[TRACTType]=3,[tblCHBK Transactions].[TRACTAmount]))))


The Null to Zero function should take care of null values being returned and not being able to be evaluated by the CCur function.


Steve

 

[awl]

Thank you very much. It all works out well and thanks for your professionalism which was a simple and down to the point response. Curtis....

 

[SteveR77]

No problem at all Curtis.

Simple tends to work best for me. Glad I could help you out.


Steve B-)