Quirky Query

D

dmoney

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
 
D

Daryl S

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?
 
D

dmoney

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 said:
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 said:
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
Click to expand...
Click to expand...
 
D

Daryl S

OK, glad you got it working!

--
Daryl S


dmoney said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

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*'));"
Click to expand...

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)
 
Top