How to keep Currency Format when using NZ()

[PNGBill]

Hi Forum, Access 2000

Just tidying up some issues and one is that a form control won't show data as #,##0.00 (currency) when the datasource sql uses NZ([fieldname],0)

This test sql has two fields. One uses NZ and the other doesn't. The NZ field just shows simple 2 decimal integers rather then full currency with thousand and "$" symbol where as the none NZ field does return data with "$" and thousand separator.

SELECT TBLTRANS.LDPK, Sum(NZ([TRNDR],0)) AS LateFees, Sum(TBLTRANS.TRNDR) AS SumOfTRNDR
FROM TBLTRANS
GROUP BY TBLTRANS.LDPK, TBLTRANS.TRNTYP
HAVING (((TBLTRANS.TRNTYP)="late fee" Or (TBLTRANS.TRNTYP)="Legal Fees"));

Tried to include Format in the sql but it throw and error

Any ideas please

 

[PNGBill]

Found a Work Around by using this as the Record Source of the Form Control

=CCur([LateFees])

This put the data back to Currency as far as the Form display is concerned.

 

[PNGBill]

Ccur() also works in the sql so no need to format in the form

SELECT TBLTRANS.LDPK, Sum(CCur(NZ([TRNDR],0))) AS LateFees, Sum(TBLTRANS.TRNDR) AS SumOfTRNDR
FROM TBLTRANS
GROUP BY TBLTRANS.LDPK, TBLTRANS.TRNTYP
HAVING (((TBLTRANS.TRNTYP)="late fee" Or (TBLTRANS.TRNTYP)="Legal Fees"));

 

[nikhilstephen]

Thank you so much for sharing the answer to this! I was looking for a function to do the exact cleanup.