William Benson
vbacreations at gmail.com
Thu Sep 8 20:47:46 CDT 2011
It makes much intuitive sense to allow either. Almost hard to see why implement it any other way. On Sep 7, 2011 4:06 PM, "Mark A Matte" <markamatte at hotmail.com> wrote: > > 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 > > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com