Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2018, 10:45 AM   #1
Ori.luxi
Newly Registered User
 
Join Date: Oct 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Ori.luxi is on a distinguished road
Unhappy Having clause in VBA

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?

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

Ori.luxi is offline   Reply With Quote
Old 10-13-2018, 11:10 AM   #2
MajP
Newly Registered User
 
Join Date: May 2018
Posts: 479
Thanks: 6
Thanked 115 Times in 113 Posts
MajP will become famous soon enough
Re: Having clause in VBA

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
MajP is offline   Reply With Quote
Old 10-13-2018, 11:20 AM   #3
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Having clause in VBA

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"

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-13-2018 at 11:34 AM.
June7 is offline   Reply With Quote
Old 10-13-2018, 11:20 AM   #4
Ori.luxi
Newly Registered User
 
Join Date: Oct 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Ori.luxi is on a distinguished road
Re: Having clause in VBA

Quote:
Originally Posted by MajP View Post
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:

Quote:
SELECT [Costumer], [Product Name], [Product Description], count([Product Description]) AS [Count]
FROM [Table] 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
Ori.luxi is offline   Reply With Quote
Old 10-13-2018, 11:28 AM   #5
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Having clause in VBA

Post #4 was moderated. Posting this to trigger email notifications
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Web links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 10-13-2018, 11:32 AM   #6
Ori.luxi
Newly Registered User
 
Join Date: Oct 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Ori.luxi is on a distinguished road
Re: Having clause in VBA

Quote:
Originally Posted by June7 View Post
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?
Ori.luxi is offline   Reply With Quote
Old 10-13-2018, 11:39 AM   #7
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Having clause in VBA

Product1 and Product2 are fields/controls on form?
Code:
strSql = "SELECT [Costumer], [Product Name], [Product Description], Count(*) AS CountProduct" & _
" 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"?

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

Last edited by June7; 10-13-2018 at 12:42 PM.
June7 is offline   Reply With Quote
Old 10-13-2018, 12:11 PM   #8
Ori.luxi
Newly Registered User
 
Join Date: Oct 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Ori.luxi is on a distinguished road
Re: Having clause in VBA

Quote:
Originally Posted by June7 View Post
Product1 and Product2 are fields/controls on form?
Code:
strSql = "SELECT [Costumer], [Product Name], [Product Description], Count(*) AS CountProduct" & _
" 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)
Ori.luxi is offline   Reply With Quote
Old 10-13-2018, 12:45 PM   #9
June7
Newly Registered User
 
Join Date: Mar 2014
Posts: 728
Thanks: 0
Thanked 168 Times in 168 Posts
June7 will become famous soon enough
Re: Having clause in VBA

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.

__________________
To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Where clause ChipRivers Queries 3 11-17-2011 07:37 AM
SQL where clause within a SUM clause in the SELECT statement chris-uk-lad General 3 07-21-2008 05:01 AM
SQL where clause within a SUM clause in the SELECT statement chris-uk-lad General 1 07-21-2008 03:37 AM
Where clause Lyncroft Forms 2 08-16-2003 09:39 AM
Where clause with Or DBL Modules & VBA 2 02-06-2003 04:56 AM




All times are GMT -8. The time now is 04:52 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World