FormatCurrency not returning formatted amount as expected?

[GraeagleBill]

tempCredit is Dim'd as Currency

                tempCredit = FormatCurrency(Me.tbNewCredit.Value)
                MsgBox "Credit amount, " & tempCredit & ", exceeds current balance in account."

When 5000 is entered into text box tbNewCredit, I'm expecting the FormatCurrency function to return $5,000. I still get 5000. What am I missing here?

[CJ_London]

sounds like formatcurrency is a udf - so the problem may be there

or perhaps tempcredit is the wrong datatype - what does does formatcurrncy function return? string? number?

[orange]

What do you get with Msgbox "Temp Credit is : " & tempCredit

Perhaps you need Format (tempCredit, "Currency")??

[GraeagleBill]

tempCredit had to be Dim'd string NOT currency

                tempCredit = FormatCurrency(Me.tbNewCredit)
                MsgBox "Credit amount, " & tempCredit & ", exceeds current balance in account."

[Missinglinq]

As Ajax suggested, FormatCurrency() is apparently a User-Defined Function, and I'm guessing that it uses Access' native Format() function.

And, FYI, the reason that tempCredit had to be dimmed as a String, is that Format() always returns a String, regardless of the DataType of the data being formatted!

If you then need to use the formatted data as it was originally meant to be used, you can wrap it in the appropriate Type Conversion Function, such as CDbl(), CDate() or, in this case, as CCur().

Linq ;0)>

[GraeagleBill]

As Ajax suggested, FormatCurrency() is apparently a User-Defined Function

Linq ;0)>

No. "FormatCurrency" is a native function in A2003, not user defined. However, its HELP text said nothing about the return attributes of the function. Anyway, after looking at the "Format" function, I concluded that "tempCredit" needed to be Dim'd string as would be required with "Format".

Bill

[Missinglinq]

Amazing! In fifteen years of working with Access, and 10 years trolling this and half-a-dozen other Access forums, I've never run into FormatCurrency! OF course, that's why I find Access to be so interesting...if you use it much you're always learning something new!

My guess would be that it uses the same low level code as the Format() function, and since it allows such things as adding Parens around negative numbers, it obviously has to return a String.

Linq ;0)>

[ItsMe]

The currency data type should be able to accept a numeric value and or a properly formatted string. I do not remember Access 2003 being any different, but I suppose it might be.

Dim curSum As Currency
curSum = 5.5

Debug.Print curSum
Debug.Print FormatCurrency(900.2, 2, vbTrue, vbTrue)

curSum = FormatCurrency(900.2, 2, vbTrue, vbTrue)

Debug.Print curSum

[GraeagleBill]

Best references:

Format:
https://msdn.microsoft.com/en-us/lib.../gg251755.aspx
and here:
FormatCurrency:
https://msdn.microsoft.com/EN-US/lib.../gg264331.aspx