[AccessD] String with "#" in VBA?

Here is what I ended up using.

gstrSQL = vbNullString
gstrSQL = gstrSQL & "SELECT"
gstrSQL = gstrSQL & " Activity,"
gstrSQL = gstrSQL & " SUM(Activity_CF) AS Total_CF"
gstrSQL = gstrSQL & " FROM ProbC_tblActivities"
gstrSQL = gstrSQL & " GROUP BY Activity"
gstrSQL = gstrSQL & " HAVING Activity='#Input'"


The rest of the code was not required.
Cheers
Darryl.



-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: Wednesday, 7 September 2011 1:05 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] String with "#" in VBA?


Hmmmm......  time for a coffee.

Thanks guys... Stupid!

Got it working fine now I have the my syntax and commas in place.

Cheers
Darryl




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Wednesday, 7 September 2011 12: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

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