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

[Alan Lane]

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

[Tom van Stiphout]

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