Currency Format in a query

P

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.
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Field won't show as currency in the form, shows just a number (but works fine in the query) 1
calculated currency field sorts like text instead of numeric 4
Null value to "0" / Currency format 2
Format currency in a query 2
Query Field Format 2
Currency Formatting Union Query 1
format field in make table query 2
Access Expression Builder 0

Top