format number

[Guest]

I know how to format a number to get 0 decimal places using the following:

SELECT DISTINCT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Format(Avg([resolveddt]-[popenterdt]),"#.#") AS days

I just need to know how to format a number like 3456723 to get 3,456,723

Please keep in mind that I am using a union query, so simpy using the
properties window to select "standard" will not work in this case.

How can I do this?

Thanks in advance,
geebee

 

[Guest]

Format(SomeNumber,"#,###")

 

[Guest]

hi,

I now have
Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706c.[SumOfCountOfLoan Acct #]),"#,###.#")

The bad thing is that if the number is something like a whole number like
45, it displays as "45."
If the number does indeed have a decimal place, it has "45.3"

I need to know how to not display the decimal if the number is indeed a
whole number.

Thanks in advance,
geebee

Format(SomeNumber,"#,###")

I know how to format a number to get 0 decimal places using the following:

SELECT DISTINCT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Format(Avg([resolveddt]-[popenterdt]),"#.#") AS days

I just need to know how to format a number like 3456723 to get 3,456,723

Please keep in mind that I am using a union query, so simpy using the
properties window to select "standard" will not work in this case.

How can I do this?

Thanks in advance,
geebee

 

[Guest]

Here is a little trick that will do that for your.

format(x,iif(x\1 = x, "#,##0", "#,###0.00"))

The \ is like dividing with the / execpt it drops the decimals. So if x= 45
then
x\1 returns 45. If x is 45.3 then x\1 still returns 45, but now it is not
equal to the original number and will use the second format option.
Note I changed the format string a bit so you will get a zero instead of a
blank if the number is zero. Depending on how you want it displayed, you may
want to change it.

hi,

I now have:
Format(Avg(resolvedno_vs_noofCACScalls_betweebpopenterdtandresolveddt_R706c.[SumOfCountOfLoan Acct #]),"#,###.#")

The bad thing is that if the number is something like a whole number like
45, it displays as "45."
If the number does indeed have a decimal place, it has "45.3"

I need to know how to not display the decimal if the number is indeed a
whole number.

Thanks in advance,
geebee

Format(SomeNumber,"#,###")

I know how to format a number to get 0 decimal places using the following:

SELECT DISTINCT Tbl_Resolved.ResolvedStatus, Tbl_Resolved.ResolvedDt,
Format(Avg([resolveddt]-[popenterdt]),"#.#") AS days

I just need to know how to format a number like 3456723 to get 3,456,723

Please keep in mind that I am using a union query, so simpy using the
properties window to select "standard" will not work in this case.

How can I do this?

Thanks in advance,
geebee