[AccessD] String with "#" in VBA?

I had to think twice...
 
Me and SQL spend our days between SQL Server, Access, and Foxpro...last week I was moving some processes from Foxpro to SQL Server and I learned that in Foxpro the HAVING clause will utilize the calculation or the alias...
 
just FYI...
 
Mark M.
 
 

> From: vbacreations at gmail.com
> To: accessd at databaseadvisors.com
> Date: Tue, 6 Sep 2011 23:41:41 -0400
> Subject: Re: [AccessD] String with "#" in VBA?
> 
> No more than anyone else going from the top of their head. To wit, no one
> else pointed out that the Having Clause required the actual computation.
> And, no one pointed out (including me) that most non-calculated constraints
> are put in a WHERE clause, not the Having clause. I would imagine the query
> plan Access builds handles that kind of misappropriation without fuss
> however. Some SQL which I generated using only the Access query designer
> (with unneeded parens removed).
> 
> SELECT Max(Tbl_GIB.LASTMODIFIED_DATE) AS MyMaxDate, 1+1 AS SomeCalc
> FROM Tbl_GIB
> WHERE Tbl_GIB.SITE_DB<>'Bill'
> HAVING 1+1=2 And Max(Tbl_GIB.LASTMODIFIED_DATE) < #12/31/2050#
> 
> This fails:
> 
> SELECT Max(Tbl_GIB.LASTMODIFIED_DATE) AS MyMaxDate, 1+1 AS SomeCalc
> FROM Tbl_GIB
> WHERE Tbl_GIB.SITE_DB<>'Bill'
> HAVING SomeCalc =2
> 
> 
> As does this
> 
> SELECT Max(Tbl_GIB.LASTMODIFIED_DATE) AS MyMaxDate, 1+1 AS SomeCalc
> FROM Tbl_GIB
> HAVING MyMaxDate < #12/31/2050#;
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
> Sent: Tuesday, September 06, 2011 11:24 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] String with "#" in VBA?
> 
> Yes, that syntax would be correct Bill. Man, I made a mess of this one
> didn't I... hmmmmm.
> 
> 
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Benson
> (VBACreations.Com)
> Sent: Wednesday, 7 September 2011 1:16 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [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
> 
> -- 
> 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