Having clause in VBA (1 Viewer)

Ori.luxi

New member
Local time
Today, 16:59
Joined
Oct 13, 2018
Messages
4
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? :confused:

Code:
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

You've got your good things, and you've got mine.
Local time
Today, 10:59
Joined
May 21, 2018
Messages
8,463
can you add a debug.print so we can see the rendered string?
Code:
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

AWF VIP
Local time
Today, 06:59
Joined
Mar 9, 2014
Messages
5,423
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"
 
Last edited:

Ori.luxi

New member
Local time
Today, 16:59
Joined
Oct 13, 2018
Messages
4
can you add a debug.print so we can see the rendered string?
Code:
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

MVP / VIP
Local time
Today, 14:59
Joined
Jan 14, 2017
Messages
18,186
Post #4 was moderated. Posting this to trigger email notifications
 

Ori.luxi

New member
Local time
Today, 16:59
Joined
Oct 13, 2018
Messages
4
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

AWF VIP
Local time
Today, 06:59
Joined
Mar 9, 2014
Messages
5,423
Product1 and Product2 are fields/controls on form?
Code:
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"?
 
Last edited:

Ori.luxi

New member
Local time
Today, 16:59
Joined
Oct 13, 2018
Messages
4
Product1 and Product2 are fields/controls on form?
Code:
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

AWF VIP
Local time
Today, 06:59
Joined
Mar 9, 2014
Messages
5,423
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.
 

Users who are viewing this thread

Top Bottom