Quirky Query
in [Queries]
|
From: dmoney on 29 Mar 2010 13:03 The query below is giving me fits. I am using vba to pull in data from access. The query below blows up when I use the variable plnt in the having clause. & "HAVING (((zbrdist.Plnt)='" & plnt & "' I have several other queries that have the same clause and no problems. Any ideas? Here is the full string strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, DistributedAnalyst.Comments, DistributedAnalyst.Category, DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ & "FROM ((zbrdist LEFT JOIN PeggTassQuerry ON zbrdist.PurchaseDoc = PeggTassQuerry.[Purchase Doc]) LEFT JOIN Controllers ON zbrdist.Material = Controllers.Material) LEFT JOIN DistributedAnalyst ON zbrdist.Material = DistributedAnalyst.Material " _ & "GROUP BY zbrdist.Plnt, zbrdist.Material, Controllers.Name, Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, DistributedAnalyst.Comments, DistributedAnalyst.Category, DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ & "HAVING (((zbrdist.Plnt)='" & plnt & "' Or (zbrdist.Plnt) Is Null) AND ((zbrdist.[Grouping WBS el]) Not Like 'v*'));" Thanks
From: Daryl S on 29 Mar 2010 13:59 Dmoney - Since the HAVING clause has NO aggregate items, you can put all of these in the WHERE clause instead. Have you tried a debug.print strsql on this after it is built to make sure it is what you expect? -- Daryl S "dmoney" wrote: > The query below is giving me fits. I am using vba to pull in data from access. > > The query below blows up when I use the variable plnt in the having clause. > & "HAVING (((zbrdist.Plnt)='" & plnt & "' > > I have several other queries that have the same clause and no problems. > Any ideas? > > Here is the full string > > strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue > TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, > zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, > DistributedAnalyst.Comments, DistributedAnalyst.Category, > DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ > & "FROM ((zbrdist LEFT JOIN PeggTassQuerry ON zbrdist.PurchaseDoc = > PeggTassQuerry.[Purchase Doc]) LEFT JOIN Controllers ON zbrdist.Material = > Controllers.Material) LEFT JOIN DistributedAnalyst ON zbrdist.Material = > DistributedAnalyst.Material " _ > & "GROUP BY zbrdist.Plnt, zbrdist.Material, Controllers.Name, > Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], > DistributedAnalyst.Analyst, DistributedAnalyst.Comments, > DistributedAnalyst.Category, DistributedAnalyst.Date, > DistributedAnalyst.Actionee " _ > & "HAVING (((zbrdist.Plnt)='" & plnt & "' Or (zbrdist.Plnt) Is Null) AND > ((zbrdist.[Grouping WBS el]) Not Like 'v*'));" > > > > Thanks
From: dmoney on 29 Mar 2010 14:06 i marked you as helpfule for the good advice on the where clause but i just figured out what the problem was -- the variable type string did not match the field type i was querying. i converted to number data type and all is well - I appreciate your response "Daryl S" wrote: > Dmoney - > > Since the HAVING clause has NO aggregate items, you can put all of these in > the WHERE clause instead. > > Have you tried a debug.print strsql on this after it is built to make sure > it is what you expect? > > -- > Daryl S > > > "dmoney" wrote: > > > The query below is giving me fits. I am using vba to pull in data from access. > > > > The query below blows up when I use the variable plnt in the having clause. > > & "HAVING (((zbrdist.Plnt)='" & plnt & "' > > > > I have several other queries that have the same clause and no problems. > > Any ideas? > > > > Here is the full string > > > > strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue > > TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, > > zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, > > DistributedAnalyst.Comments, DistributedAnalyst.Category, > > DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ > > & "FROM ((zbrdist LEFT JOIN PeggTassQuerry ON zbrdist.PurchaseDoc = > > PeggTassQuerry.[Purchase Doc]) LEFT JOIN Controllers ON zbrdist.Material = > > Controllers.Material) LEFT JOIN DistributedAnalyst ON zbrdist.Material = > > DistributedAnalyst.Material " _ > > & "GROUP BY zbrdist.Plnt, zbrdist.Material, Controllers.Name, > > Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], > > DistributedAnalyst.Analyst, DistributedAnalyst.Comments, > > DistributedAnalyst.Category, DistributedAnalyst.Date, > > DistributedAnalyst.Actionee " _ > > & "HAVING (((zbrdist.Plnt)='" & plnt & "' Or (zbrdist.Plnt) Is Null) AND > > ((zbrdist.[Grouping WBS el]) Not Like 'v*'));" > > > > > > > > Thanks
From: Daryl S on 29 Mar 2010 15:23 OK, glad you got it working! -- Daryl S "dmoney" wrote: > i marked you as helpfule for the good advice on the where clause but i just > figured out what the problem was -- the variable type string did not match > the field type i was querying. i converted to number data type and all is > well - I appreciate your response > > > "Daryl S" wrote: > > > Dmoney - > > > > Since the HAVING clause has NO aggregate items, you can put all of these in > > the WHERE clause instead. > > > > Have you tried a debug.print strsql on this after it is built to make sure > > it is what you expect? > > > > -- > > Daryl S > > > > > > "dmoney" wrote: > > > > > The query below is giving me fits. I am using vba to pull in data from access. > > > > > > The query below blows up when I use the variable plnt in the having clause. > > > & "HAVING (((zbrdist.Plnt)='" & plnt & "' > > > > > > I have several other queries that have the same clause and no problems. > > > Any ideas? > > > > > > Here is the full string > > > > > > strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue > > > TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, > > > zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, > > > DistributedAnalyst.Comments, DistributedAnalyst.Category, > > > DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ > > > & "FROM ((zbrdist LEFT JOIN PeggTassQuerry ON zbrdist.PurchaseDoc = > > > PeggTassQuerry.[Purchase Doc]) LEFT JOIN Controllers ON zbrdist.Material = > > > Controllers.Material) LEFT JOIN DistributedAnalyst ON zbrdist.Material = > > > DistributedAnalyst.Material " _ > > > & "GROUP BY zbrdist.Plnt, zbrdist.Material, Controllers.Name, > > > Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], > > > DistributedAnalyst.Analyst, DistributedAnalyst.Comments, > > > DistributedAnalyst.Category, DistributedAnalyst.Date, > > > DistributedAnalyst.Actionee " _ > > > & "HAVING (((zbrdist.Plnt)='" & plnt & "' Or (zbrdist.Plnt) Is Null) AND > > > ((zbrdist.[Grouping WBS el]) Not Like 'v*'));" > > > > > > > > > > > > Thanks
From: John W. Vinson on 29 Mar 2010 16:26 On Mon, 29 Mar 2010 10:03:02 -0700, dmoney <dmoney(a)discussions.microsoft.com> wrote: >The query below is giving me fits. I am using vba to pull in data from access. > >The query below blows up when I use the variable plnt in the having clause. >& "HAVING (((zbrdist.Plnt)='" & plnt & "' > > I have several other queries that have the same clause and no problems. >Any ideas? > >Here is the full string > >strsql = "SELECT zbrdist.Plnt, Sum(zbrdist.[Value TCur]) AS [SumOfValue >TCur], zbrdist.Material, Controllers.Name, Controllers.Telephone, >zbrdist.[WBS Element], zbrdist.[Grouping WBS el], DistributedAnalyst.Analyst, >DistributedAnalyst.Comments, DistributedAnalyst.Category, >DistributedAnalyst.Date, DistributedAnalyst.Actionee " _ >& "FROM ((zbrdist LEFT JOIN PeggTassQuerry ON zbrdist.PurchaseDoc = >PeggTassQuerry.[Purchase Doc]) LEFT JOIN Controllers ON zbrdist.Material = >Controllers.Material) LEFT JOIN DistributedAnalyst ON zbrdist.Material = >DistributedAnalyst.Material " _ >& "GROUP BY zbrdist.Plnt, zbrdist.Material, Controllers.Name, >Controllers.Telephone, zbrdist.[WBS Element], zbrdist.[Grouping WBS el], >DistributedAnalyst.Analyst, DistributedAnalyst.Comments, >DistributedAnalyst.Category, DistributedAnalyst.Date, >DistributedAnalyst.Actionee " _ >& "HAVING (((zbrdist.Plnt)='" & plnt & "' Or (zbrdist.Plnt) Is Null) AND >((zbrdist.[Grouping WBS el]) Not Like 'v*'));" For one thing, you can change the HAVING keyword to WHERE: WHERE filters records based on actual table values, *before* the totalling and grouping are done; HAVING does all the totals and *then* filters. What's the datatype of zbrdist.Plnt? If it's a number field, you need to remove the quotemarks: WHERE (((zbrdist.Plnt)=" & plnt & " Or (zbrdist.Plnt) Is Null) -- John W. Vinson [MVP]
|
Pages: 1 Prev: Crosstab query question Next: Calculating hours on a timeclock form |