Format lost after concatenation

[DBAchick]

Running Access 2000/W2K

Unbound Textbox on a report has the following:

Control Source: =IIf([DTCurrAmtGL]=0,[DTCurrRate],[DTCurrAmtGL])
Format: Standard
Decimal Places: 3

In the table with these field in them, DTCurrAmtGL is a standard datatype and the data is stored as xxx,xxx

DTCurrRate is a decimal datatype and the data is stored as x.xxx

Everything works fine except that if the CurrAmt field is displayed, I wanted a '$' in front of the number. So, I did this:

Control Source: =IIf([DTCurrAmtGL]=0,[DTCurrRate],"$" & [DTCurrAmtGL])

Now, when the CurrAmt field is displayed it drops the comma!
Result = $xxxxxx

I have played with the datatypes on the textbox, in the table and decimal places. Nothing gives me the desired result.

Desired result = DTCurrRate displays as x.xxx and DTCurrAmtGL displays as $xxx,xxx

Any help is appreciated.

Thanks!

 

[MichaelRed]

When you do the concatenation Ms. Access 'equalizes' the data types (otherwise it cannot concatenate a numeric and a string). In this instance, the 'lowest common denominator' (e.g. STRING) wins and the value is converted to text before concatenation. Since the previous FORMAT applied to the numeric value - ipso gono. And Bye Bye little comma birdie!

Look at the Format FUNCTION for the proper appproach.

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over