Why won't CCur() make the value be currency?

[Seth Schrock]

I have an unbound textbox that I want to have part of it be a dollar amount, but it isn't working. Here is the code in textbox:

="* " & [NumToGetSpecialRate] & 
Switch([NumToGetSpecialRate]=1,"st",[NumToGetSpecialRate]=2,"nd",[NumToGetSpecialRate]=3,"rd",[NumToGetSpecialRate]>=4,"th") & 
" file of same month " & 
CCur([SpecialRate])

The last line, CCur([SpecialRate]) should give a dollar amount, but it isn't. I get just a number with no decimal. It does return the correct number, just not as a dollar amount. For example, one record returns this value * 3rd file of same month 12, but I want to be * 3rd file of same month $12.00.


Does CCur() work with variables but not with fields? Is there a better way to do this?

[NeoPa]

CCur() is working perfectly as expected. It converts the argument to a Currency value. Of course, as soon as you add that Currency value to the end of the string it is automatically re-converted to string in the standard way, which does not include determining a format string from the type of the value it used to be. Does that make sense?

What you need to do here is to use the Format() function to prepare the string representation of your value :

='* ' & [NumToGetSpecialRate] &
Switch([NumToGetSpecialRate]=1,'st',[NumToGetSpecialRate]=2,'nd',[NumToGetSpecialRate]=3,'rd',[NumToGetSpecialRate]>=4,'th') & 
' file of same month ' & 
Format([SpecialRate],'Currency')

[Seth Schrock]

That does make sense. Thanks, and it works perfectly.

[NeoPa]

Excellent :-)