Having clause in VBA

[Ori.luxi]

Hello,

My first post

I would like to write the following SQL in VBA :

Return all Costumers who bought BETWEEN 2 AND 6 TV's
AND also bought BETWEEN 2 AND 6 Cameras

1. Until the HAVING part - all working fine! What am I doing wrong?
2. Is it possible to add multiple conditions to HAVING?

Sub SQLinVBA ()

Product1 = TV
Product2 = Camera


" SELECT [Costumer], [Product Name], [Product Description], 
  count([Product Description]) " & _
" FROM Table " & _
" WHERE [Product Description] IN (" & Chr$(39) & Product1 & Chr$(39) & "," & Chr$(39) & Product2 & Chr$(39) & ") " & _
" GROUP BY [Costumer], [Product Name], [Product Description] " & _
" HAVING count([Product Description]) BETWEEN 2 AND 6 AND [Product Description] = " Product1 & _
" AND " & _
" HAVING count([Product Description]) BETWEEN 8 AND 12 AND [Product Description] = " Product2

End sub

 

[MajP]

can you add a debug.print so we can see the rendered string?

dim strSql as string
Product1 = TV
Product2 = Camera

strSql = SELECT [Costumer], [Product Name], [Product Description], 
  count([Product Description]) " & _
" FROM Table " & _
" WHERE [Product Description] IN (" & Chr$(39) & Product1 & Chr$(39) & "," & Chr$(39) & Product2 & Chr$(39) & ") " & _
" GROUP BY [Costumer], [Product Name], [Product Description] " & _
" HAVING count([Product Description]) BETWEEN 2 AND 6 AND [Product Description] = " Product1 & _
" AND " & _
" HAVING count([Product Description]) BETWEEN 8 AND 12 AND [Product Description] = " Product2
debug.print strSql
End sub

 

[June7]

No, there can be only one WHERE clause and one HAVING clause. Since the Product Description criteria is in WHERE clause, don't think is needed in HAVING.

Product1 and Product2 are fields/controls on form?

" WHERE [Product Description] IN ('" & Me.Product1 & "','" & Me.Product2 & "')" & _

Count can never be both "BETWEEN 2 AND 6" and "BETWEEN 8 AND 12", it will be one or the other for each record. Use OR operator.

" HAVING Count([Product Description]) BETWEEN 2 AND 6" & _
" OR " & _
" Count([Product Description]) BETWEEN 8 AND 12"

 

[Ori.luxi]

can you add a debug.print so we can see the rendered string?

dim strSql as string
Product1 = TV
Product2 = Camera

strSql = SELECT [Costumer], [Product Name], [Product Description], 
  count([Product Description]) " & _
" FROM Table " & _
" WHERE [Product Description] IN (" & Chr$(39) & Product1 & Chr$(39) & "," & Chr$(39) & Product2 & Chr$(39) & ") " & _
" GROUP BY [Costumer], [Product Name], [Product Description] " & _
" HAVING count([Product Description]) BETWEEN 2 AND 6 AND [Product Description] = " Product1 & _
" AND " & _
" HAVING count([Product Description]) BETWEEN 8 AND 12 AND [Product Description] = " Product2
debug.print strSql
End sub

I added couple of spaces before/after every string to make sure they won't concat.
debug prints this:

SELECT [Costumer], [Product Name], [Product Description], count([Product Description]) AS [Count]
FROM

WHERE [Product Description] IN (Product1, Product2)
GROUP BY [Costumer], [Product Name], [Product Description]
HAVING [Product Description] BETWEEN 2 AND 6 AND [Product Description] = Product1
AND
HAVING [Product Description] BETWEEN 8 AND 12 AND [Product Description] = Product2

 

[isladogs]

Post #4 was moderated. Posting this to trigger email notifications

 

[Ori.luxi]

No, there can be only one WHERE clause and one HAVING clause. Remove the second HAVING word. Since the Product Description criteria is in WHERE clause, don't think is needed in HAVING.

Count can never be both "BETWEEN 2 AND 6" and "BETWEEN 8 AND 12", it will be one or the other for each record. Use OR operator.

" HAVING Count([Product Description]) BETWEEN 2 AND 6" & _
" OR " & _
" Count([Product Description]) BETWEEN 8 AND 12"

Even with 1 HAVING clause i am getting an error
My need is to obtain all customers who bought 2-6 TV's AND 8-12 Cameras
Can I write it differently?

 

[June7]

Product1 and Product2 are fields/controls on form?

strSql = "SELECT [Costumer], [Product Name], [Product Description], Count(*) [COLOR=red]AS CountProduct[/COLOR]" & _
" FROM Table " & _
" WHERE [Product Description] IN ('" & Me.Product1 & "','" & Me.Product2 & "')" & _
" GROUP BY [Costumer], [Product Name], [Product Description] " & _
" HAVING Count([Product Description]) BETWEEN 2 AND 6" & _
" OR " & _
" Count([Product Description]) BETWEEN 8 AND 12"

The AND operator in this simple SELECT query won't get you customers (costumer?) that have bought BOTH items. That will require a subquery.

Is your table really named "Table"?

 

[Ori.luxi]

Product1 and Product2 are fields/controls on form?

strSql = "SELECT [Costumer], [Product Name], [Product Description], Count(*) [COLOR=red]AS CountProduct[/COLOR]" & _
" FROM Table " & _
" WHERE [Product Description] IN ('" & Me.Product1 & "','" & Me.Product2 & "')" & _
" GROUP BY [Costumer], [Product Name], [Product Description] " & _
" HAVING Count([Product Description]) BETWEEN 2 AND 6" & _
" OR " & _
" Count([Product Description]) BETWEEN 8 AND 12"

The AND operator in this simple SELECT query won't get you customers (costumer?) that have bought BOTH items. That will require a subquery.

If your table really named "Table"?

Thank you.
Product 1 and 2 are variables. Product 1 = 'TV and Product 2 = 'Camera'
How do I do that with a subquery? can you show me an example (SQL w/o VBA)

 

[June7]

Possibly:

" WHERE [Product Description] = " & Product1 & " AND [Costumer] IN (SELECT [Costumer] FROM Table WHERE [Product Description]='" & Product2 & "')" & _


Suggest you change spelling of Costumer to Customer throughout database.