Currency Format in a query

[PJ]

I have the following expression in a query and I want to format it for
currency.

SumOfPartAmount: Sum(Val(nulltozero(([PartAmount]))))

My return comes out as: 2500000.00
I Want it to come out at: $2,500,000.00

How would I do this in a query? This query is being used in a form and it
will not format to currency in the form.

 

[John Spencer MVP]

Assuming PartAmount is a text field then you could use

Format(Sum(Val(Nz(PartAmount,0))),"$#,###.00")

Or my preference would be to use
Sum(Val(PartAmount & ""))
and apply the format using the form control's format property.

If you still can't do that using the form control's format property, try
CCur(Sum(Val(PartAmount & "")))
in the query and then you should be able to use the form control's format
property if needed.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County