Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Having vs Where

Posted on 2004-04-03
9
Medium Priority
?
226 Views
Last Modified: 2009-12-16
Hi,

I know the basic difference between HAVING and WHERE.  In terms of the speed, does it make a difference?  Thanks
0
Comment
Question by:mcrmg
  • 4
  • 4
9 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 10751216
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
 

Author Comment

by:mcrmg
ID: 10751266
I was wondering if you could help me with this.  Thanks
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 720 total points
ID: 10751295
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:mcrmg
ID: 10751297
I was missing the "duration" Part...  Thank you
0
 

Author Comment

by:mcrmg
ID: 10751319
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
 
LVL 54

Expert Comment

by:nico5038
ID: 10751329
Try just:

 CurrentDb.execute("SELECT * FROM Loan")

Nic;o)
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 400 total points
ID: 10752060
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
 

Author Comment

by:mcrmg
ID: 10752160
Yes, it works fine.  How can I call a query that I built in query builder?  Thx
0
 
LVL 54

Expert Comment

by:nico5038
ID: 10752241
Just use the name in:
Set rs = CurrentDb.OpenRecordset("<queryname>")

Nic;o)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question