Format to Percent in query 2

[dbero]

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

 

[acent]

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

 

[dbero]

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

 

[acent]

So the number itself is wrong or just the formatting?


"If it's stupid but works, it isn't stupid."
-Murphy's Military Laws

 

[dbero]

Yes, the formatting is wrong. I want the value in the query to be 95%, for example rather than .95.

 

[acent]

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

 

[jrbarnett]

Format ([QRY count sold]/[QRY total purchased]*100,"0") & "%"

should do it.

John

 

[molly]

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

 

[jrbarnett]

Change "0" to "0.0" for one decimal place.

John

 

[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

 

[jrbarnett]

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