Currency in Query Not Formatted

[yimi]

I have a query that uses two Query's. In one of my fields I have

CurrentSumOfPaymentAmount: IIf(IsNull([SumOfPaymentAmount]),"0 ",[SumOfPaymentAmount])

to return a zero if null. The problem is I need it to return currency. (The data that is correctly formatted in one query even returns unformatted.) When I select the properties for that field I don't have an option to select currency. The drop down is blank.

WHAT'S UP?!?!

 

[yimi]

OK,

Here's what I did.

CurrentSumOfPaymentAmount: Format([SumOfPaymentAmount],'$') & IIf(IsNull([SumOfPaymentAmount]),"0 ",[SumOfPaymentAmount])

It seems to be working but it looks pretty crazy to me.

Thanks

 

[Gazonice]

Hi,

You may have to use a custom module to format currency, esp in queries and on forms:


'******************************************************
' Declarations section of the module
'******************************************************

Option Explicit
Const Factor = 100

'=====================================================
' RoundAU and TruncAU are designed to be added to the
' AfterUpdate property on a form control.
'=====================================================
Function RoundAU(X As Control)

X = Int(X * Factor + 0.5) / Factor

End Function

Function TruncAU(X As Control)
X = Int(X * Factor) / Factor
End Function

'=====================================================
' RoundCC and TruncCC are designed to be used in
' expressions and calculated controls on forms and reports.
'=====================================================
Function RoundCC(X)

RoundCC = Int(X * Factor + 0.5) / Factor

End Function

Function TruncCC(X)
TruncCC = Int(X * Factor) / Factor
End Function

Save the module as: curr_trunc

Then to invoke the module use:

(roundcc([field or formula])) to format the result using the module.

Good Luck

Garry