Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 228
  • Last Modified:

Having vs Where

Hi,

I know the basic difference between HAVING and WHERE.  In terms of the speed, does it make a difference?  Thanks
0
mcrmg
Asked:
mcrmg
  • 4
  • 4
2 Solutions
 
nico5038Commented:
Hmm, I think not, but Access surprised me more then once :-)

Just create a small test module to execute a HAVING and a WHERE groupby query a few 1,000 times in a loop with a save time and a print of the time difference when ready.
Make sure to run it several times as the system's workload can make a single measurement unreliable.

Nic;o)
0
 
mcrmgAuthor Commented:
I was wondering if you could help me with this.  Thanks
0
 
nico5038Commented:
Where's the problem?

Just create a function like:

Function fncTestTime()

Dim dtm As Date

dtm = Time()

For intI = 1 To 1000
   CurrentDb.Execute ("having query")
Next

Debug.Print "Having duration: " & Date - dtm

dtm = Time()

For intI = 1 To 1000
   CurrentDb.Execute ("where query")
Next

Debug.Print "Where duration: " & Date - dtm

End Function


Nic;o)
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
mcrmgAuthor Commented:
I was missing the "duration" Part...  Thank you
0
 
mcrmgAuthor Commented:
Hi,

I run this and it says " expect a function or variable", I thought I need to assign it to a rs??

Set rs = CurrentDb.Execute("SELECT * FROM Loan")
0
 
nico5038Commented:
Try just:

 CurrentDb.execute("SELECT * FROM Loan")

Nic;o)
0
 
Rey Obrero (Capricorn1)Commented:
I think this won't work. You can not execute a select query

       <CurrentDb.execute("SELECT * FROM Loan")>

Try it this way
Dim strSQL as String
strSQL="Select * From Loan"

Set rs = CurrentDb.OpenRecordset(strSQL)

0
 
mcrmgAuthor Commented:
Yes, it works fine.  How can I call a query that I built in query builder?  Thx
0
 
nico5038Commented:
Just use the name in:
Set rs = CurrentDb.OpenRecordset("<queryname>")

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now