[AccessD] String with "#" in VBA?

I concur with the single-quotes and the comma additions... but I was not
aware you are allowed to use the result field's name in the HAVING clause, I
thought you needed to show the function again (see below)?


gstrSQL = ""
gstrSQL = gstrSQL & " SELECT"
gstrSQL = gstrSQL & " Activity,"
gstrSQL = gstrSQL & " SUM(Activity_CF) AS Total_CF,"
gstrSQL = gstrSQL & " SUM(Activity_DCF) AS Total_DCF"
gstrSQL = gstrSQL & " FROM ProbC_tblActivities"
gstrSQL = gstrSQL & " GROUP BY Activity"
gstrSQL = gstrSQL & " HAVING Activity='#Input'"
gstrSQL = gstrSQL & " AND SUM(Activity_CF)  <> 0"




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, September 06, 2011 10:16 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] String with "#" in VBA?

As others have pointed out, the # is not the problem.  

#Input is a string inside your SQL which needs to be delimited inside your
strSQL. 
You are using double quotes as your strSQL building delimiter so the inner
variable needs to 
be surrounded by single quotes.

Also you are missing a comma after Total_CF



Try this:

gstrSQL =   "SELECT " & _
    "Activity, " & _
    "SUM(Activity_CF) AS Total_CF," & _
    "SUM(Activity_DCF) AS Total_DCF " & _
    "FROM ProbC_tblActivities " & _
    "GROUP BY Activity " & _
    "HAVING Activity='#Input' " & _
    "AND Total_CF <> 0" & _




On 7 Sep 2011 at 11:34, Darryl Collins wrote:

> Uh oh...  I thought today was going too darn well.
> 
> I am working with data which has "#" as part of the string which
> causes the VBA code to fail
> 
> For Example:
> 
> gstrSQL = vbNullString
> gstrSQL = gstrSQL & " SELECT"
> gstrSQL = gstrSQL & " Activity,"
> gstrSQL = gstrSQL & " SUM(Activity_CF) AS Total_CF"
> gstrSQL = gstrSQL & " SUM(Activity_DCF) AS Total_DCF"
> gstrSQL = gstrSQL & " FROM ProbC_tblActivities"
> gstrSQL = gstrSQL & " GROUP BY Activity"
> gstrSQL = gstrSQL & " HAVING Activity="#Input"
> gstrSQL = gstrSQL & " AND Total_CF <> 0"
> 
> Which sort of makes sense as the # is used in VBA for Conditional
> Compile.
> 
> Is there some way I can I get VBA to accept the # as part of the
> string and not a command?
> 
> Google has fairly useless when searching for "#" as well.  Bah
> humbubg.
> 
> Cheers
> Darryl
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 



-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com