[AccessD] String with "#" in VBA?

Stuart McLachlan stuart at lexacorp.com.pg
Tue Sep 6 21:16:18 CDT 2011


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
> 






More information about the AccessD mailing list