Format Function in Query - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Format Function in Query

Dan Jackson

4 years ago

Hi guys,

I'm loading a query directly into a list box to display a total figure. I need to format it using #,###. (To remove the decimal places)

The list box doesn't have a format function AND
Setting the format in the query builder doesn't save (I'm assuming because its built into the list box rather than standalone)

This is the SQL line which generates the Total figure but want to remove the decimal places:

SumOfGrossWrittenAMT: Sum(SalesALLQ.GrossWrittenAMT)

====================================================

Figured it as i was typing this! I'll share in case it helps anyone else

Expression Name: Format(Sum([Table].[Field Containing Figures]),"#,###")

Hope this helps, appreciate the help!!!

	Dan Jackson OP @Reply 4 years ago
The system removed the currency sign. Just put the currency symbol before the first #

	Kevin Robertson @Reply 4 years ago
Great Dan, You could do the same thing like this (just to give you a choice). `Expression: FormatCurrency(Sum([FieldName]),0)`

	Dan Jackson OP @Reply 4 years ago
Nice!

	Dan Jackson OP @Reply 4 years ago
I tried the below in an attempt to replace null values with 0 but i still get a blank field. Have i done it wrong or can that not be done here? Thanks SELECT Nz(FormatCurrency(Sum([SalesALLQ].[GrossWrittenAMT]),0),0) AS SumOfGrossWrittenAMT

	Adam Schwanz @Reply 4 years ago
You may need to use IIF to get the 0 to showup IIF(IsNull([FieldName]),0,"SELECT FormatCurrency(Sum([SalesALLQ].[GrossWrittenAMT]),0) AS SumOfGrossWrittenAMT")

	Kevin Robertson @Reply 4 years ago
Put the Nz after FormatCurrency. `SELECT FormatCurrency(Nz(Sum([SalesALLQ].[GrossWrittenAMT]),0),0) AS SumOfGrossWrittenAMT`

Scott Axton @Reply

4 years ago

Dan
The order of the operations is an issue IMHO.
I usually build it from the inside out like this:

First you should NZ the sales.  NZ([SalesALLQ].[GrossWrittenAMT],0)
Then sum it.    Sum(NZ([SalesALLQ].[GrossWrittenAMT],0))
Format it.   Format(Sum(NZ([SalesALLQ].[GrossWrittenAMT],0)),"Currency")
Alias it.  Format(Sum(NZ([SalesALLQ].[GrossWrittenAMT],0)),"Currency") as SumOfGrossWrittenAMT

This should work unless I mucked something up
Select Format(Sum(NZ([SalesALLQ].[GrossWrittenAMT],0)),"Currency") as SumOfGrossWrittenAMT

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago
Tried playing around with a few different things but couldn't get it to work

	Adam Schwanz @Reply 4 years ago
Oh, yea, I don't think that will work. I thought you were outside of the query now sorry. Inside the query you would do like `Expression: IIF(IsNull([FieldName]),0,FormatCurrency(Sum([FieldName]),0))`

	Dan Jackson OP @Reply 4 years ago

	Dan Jackson OP @Reply 4 years ago
Hey, just bumping this one as still unresolved. Kevin previously suggested using an if statement to show the value OR (if null) show the count but I couldn't get it work. The control displaying the figure is a list box with code. Since the form requeries every minute, could I use an after update VBA expression?

	Dan Jackson OP @Reply 4 years ago
*With code. Meant to say with no code

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View