Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Format to Percent in query 2

Status
Not open for further replies.

dbero

Technical User
Mar 31, 2005
109
US

I am trying to return from a query a value formatted as a percent. the following doesn't work and i can't find any other options. Can you help?

Thank you!

format([QRY count sold]/[QRY total purchased],"%") AS Ratio
 
As a matter of speed, I think doing the math in the sql query would be quicker. If using a text box to display, I believe there is a format property in which you can select percent.

Hope this helps.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws
 
Thank you. I am actually trying to do this in a sql statement. the SQL statement for the division now returns .93839223. That's what i cannot figure out.
thanks

 
So the number itself is wrong or just the formatting?


"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws
 
Yes, the formatting is wrong. I want the value in the query to be 95%, for example rather than .95.

 
Got it. OK... I just ran a test in Acc07. I think what you're looking for is not format() bur:
FormatPercent([QRY count sold]/[QRY total purchased])

It formatted beautifully for me.

Hope this helps.

"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws
 
Format ([QRY count sold]/[QRY total purchased]*100,"0") & "%"

should do it.

John
 
John - very interesting. I added your info to my Qry calc but i get a whole number EG 84% but i would like EG 84.4%. In other words, one decimal. what am i doing wrong? Here is my Qry calc:

Profit%: IIf([Sales$]=0 Or [Profit$]=0,"--",Format(Round(([Profit$]/[Sales])*100,1),"0") & "%")

Maybe there is further improvement needed by me?
I get the correct answer but with no decimal.

Molly
 
One last question. I now have my decimal. however, the answer is left justified and bumps up against the Profit$ column. For readablity, how can i make it right justified?
Molly
 
If this is a query directly then no, but if its bound to a control on a report or form you can set the alignment on the control to whatever you want.

There's no such thing as "Right justified." Right aligned means aligned to the right, left aligned means aligned to the left. Justified refers to text that is spaced such that it takes up the entire width of the control, and is more often used with word processor or web page documents.

John
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top