Currency Format not working

[iamnu]

Control Source: =Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"Currency")
Format: $#,##0.00;($#,##0.00)[Red]

I have also tried the following Control Source:
=Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"$#,##0.00;($#,##0.00)[Red]")

In both cases, the Color is NOT Red when the value is negative.
Can someone explain why?

 

[John W. Vinson]

Control Source: =Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"Currency")
Format: $#,##0.00;($#,##0.00)[Red]

I have also tried the following Control Source:
=Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"$#,##0.00;($#,##0.00)[Red]")

In both cases, the Color is NOT Red when the value is negative.
Can someone explain why?

Because you're tripping over your own feet. The Format() *FUNCTION* returns a
text string; applying the textbox's Format property on top of that is messing
you up (since the textbox's format property expects a numeric Currency value,
not a text string).

Try setting the control source to just txtAcctTotal itself, and use the format
property as in your first example. You may need all four format options
(positive, negative, zero and Null):

$#,##0.00;($#,##0.00)[Red];"$0.00";"Unknown"

 

[iamnu]

Control Source: =Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"Currency")
Format: $#,##0.00;($#,##0.00)[Red]

I have also tried the following Control Source:
=Format([Forms]![MedicalForm]![frmMSsubform].[Form].
[txtAcctTotal],"$#,##0.00;($#,##0.00)[Red]")

In both cases, the Color is NOT Red when the value is negative.
Can someone explain why?

Because you're tripping over your own feet. The Format() *FUNCTION* returns a
text string; applying the textbox's Format property on top of that is messing
you up (since the textbox's format property expects a numeric Currency value,
not a text string).

Try setting the control source to just txtAcctTotal itself, and use the format
property as in your first example. You may need all four format options
(positive, negative, zero and Null):

$#,##0.00;($#,##0.00)[Red];"$0.00";"Unknown"

Okay, thank you, sir!