By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,847 Members | 2,354 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,847 IT Pros & Developers. It's quick & easy.

In Access 2003, how to: output a value, followed by a percent sign (%), from a SQL query on a Report

P: n/a
Hello world:

I'm having trouble finding how to escape the percent sign ("%") from a SQL
query
so that it will show up in an Access report. Here's my SQL string ...

'--Build SQL String
strSQL = ""
strSQL = strSQL & "SELECT tblSubscriberActivity.PERIOD, "
strSQL = strSQL & "Sum(tblSubscriberActivity.AVG_SUBS) AS AVG_SUBS, "
strSQL = strSQL & "Sum(tblSubscriberActivity.NET_DEACTS) AS NET_DEACTS, "
strSQL = strSQL & "CSTR(ROUND(Sum(tblSubscriberActivity.NET_DEAC TS)/"
strSQL = strSQL & "Sum(tblSubscriberActivity.AVG_SUBS),3)*100) AS CHURN "
strSQL = strSQL & "FROM tblSubscriberActivity "
strSQL = strSQL & "GROUP BY tblSubscriberActivity.PERIOD "
strSQL = strSQL & "HAVING tblSubscriberActivity.PERIOD In (" & strPeriod &
");"

The above SQL code works and produces a nice (for example) "5.9" value for
CHURN.
However, if I want it to produce "5.9%", I can't do it. I have tried using
Chr(37), but that
doesn't work. There MUST be a way to do this! Can anyone help?

Alan
Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On Thu, 30 Dec 2004 03:26:57 GMT, "Alan Lane" <aw*****@yahoo.com>
wrote:

Set the Format property for that field in the report to Percent.
Then divide your results by 100, so you get 0.059, which will then
correctly display as 5.9%

-Tom.

Hello world:

I'm having trouble finding how to escape the percent sign ("%") from a SQL
query
so that it will show up in an Access report. Here's my SQL string ...

'--Build SQL String
strSQL = ""
strSQL = strSQL & "SELECT tblSubscriberActivity.PERIOD, "
strSQL = strSQL & "Sum(tblSubscriberActivity.AVG_SUBS) AS AVG_SUBS, "
strSQL = strSQL & "Sum(tblSubscriberActivity.NET_DEACTS) AS NET_DEACTS, "
strSQL = strSQL & "CSTR(ROUND(Sum(tblSubscriberActivity.NET_DEAC TS)/"
strSQL = strSQL & "Sum(tblSubscriberActivity.AVG_SUBS),3)*100) AS CHURN "
strSQL = strSQL & "FROM tblSubscriberActivity "
strSQL = strSQL & "GROUP BY tblSubscriberActivity.PERIOD "
strSQL = strSQL & "HAVING tblSubscriberActivity.PERIOD In (" & strPeriod &
");"

The above SQL code works and produces a nice (for example) "5.9" value for
CHURN.
However, if I want it to produce "5.9%", I can't do it. I have tried using
Chr(37), but that
doesn't work. There MUST be a way to do this! Can anyone help?

Alan


Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.