FORMAT A NUMBER - integer AND commas 2

[sohunter]

Within a query, I want to turn a number(with decimals) into an integer (no decimal places...truncated, not rounded) AND I want the number format to include commas. When I do this:

format(int(1000.5),"standard")

I get 1,000.00

I want 1,000

And I need to keep the data as a number, as I'm doing calculations on it in a report. Any way to stay in a number data type, AND remove the .00?

 

[Blorf]

Instead of "Standard" use #'s like Format(9999999.99,"#,###")

This returns 10,000,000, so if you dont want it rounded, use Format(int(9999999.99),"#,###")

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Blorf]

Additionally, if you are using the data on a report, why does the format of the query really matter? It's much easier to format data on a report than in a query, unless the query is being used for lookups also?

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[sohunter]

Thank you Blorf. Your suggestion worked perfectly.

I want to get the formatting taken care of in the query, because I'll be creating a lot of reports based on it, and this will save me the repetition.

 

[Blorf]

Noted.

Glad to help.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[dhookom]

I would still perform the formatting in the report (where it belongs). You may not get the proper results if you attempt to Sum() your formatted numbers or perform other calculations. I think the less you do in your query, the more flexibility you have in your report.



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jlitondo]

Blorf, your tip worked well for me too and dhookom's suggestion is true (speaking from experience) I thought you both deserved stars.