CODEWITHSUNDEEP
sqlms-accessms-access-2010
ChrisG
ChrisG

Reputation: 1251

Access SQL: Format a calculation as a percentage (Numeric, not string)

I'm working in Access 2010's SQL editor and I'm performing a calculation that results in a ratio.

I would like to display this ratio as a percentage using the SQL syntax editor, but as a number, not a string. I'd like to be able to paste out/export to Excel and not have to convert text to numbers.

Let's say this is my calculation:

OriginCount/DestinationCount AS MatchRate

I used the FORMAT function to make it appear as a percentage, but the result appears as a string. (Which I think is how the FORMAT function is designed to work)

FORMAT(OriginCount/DestinationCount,'Percent') AS MatchRate

Question 1: Is this possible using the SQL syntax editor? Question 2: How do I do it?

Thanks!

Upvotes: 0

Views: 5049

Answers (2)

JCro
JCro

Reputation: 696

How will you be using this data? It is standard to leave it as a double, EG: 0.02354, and then simply change the format of any control displaying that field.

Users should not being seeing tables or queries without them being the recordsource of a form, so this shouldn't be a problem.

That way, when exporting to Excel/Wherever else, it will properly display as a decimal number, and when viewing in Access, it will display as a Percentage 2.35%

Upvotes: 3

dyao
dyao

Reputation: 1011

The result of the FORMAT function in access is always in string format, so that would be expected behavior.

Did you try the CONVERT function instead?

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: -1

Related Questions