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
Location: USA baby
Posts: 1,182
Thanks: 22
Thanked 317 Times in 306 Posts
MajP will become famous soon enough 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
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,273
Thanks: 0
Thanked 307 Times in 307 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 online now   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: 8,346
Thanks: 100
Thanked 2,148 Times in 1,993 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
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.

Website 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
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
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,273
Thanks: 0
Thanked 307 Times in 307 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 online now   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
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,273
Thanks: 0
Thanked 307 Times in 307 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 online now   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 05:33 PM.


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

Featured Forum post


Sponsored Links


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