Something faster than dlookup

jdallain
jdallain used Ask the Experts™
on
Hello Experts,

In the code below I'm using the dlookup function to find the ADate (assessment date) so I can calculate the age. It works, but it's really bogging things down. I was wondering if I could use a recordset or something else to speed things up? I've tried the elookup and tlookup function that I've found by searching the EE db, but they don't seem to woork. Thanks, James
Function AAge(varBirthDate As Variant) As Integer
     Dim varAAge As Variant
     Dim dtYourDateVariable As Date
     dtYourDateVariable = DLookup("ADate", "tblNAPatient", "AID = AID")
 
    If IsNull(varBirthDate) Then AAge = 0: Exit Function
    varAAge = DateDiff("yyyy", varBirthDate, dtYourDateVariable)
    If Date < DateSerial(Year(Now), Month(varBirthDate), _
    Day(varBirthDate)) Then
    varAAge = varAAge - 1
     End If
     AAge = CInt(varAAge)
End Function

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Define 'bogging things down' ...

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
How many records are in the table?
Do you have an Index on ADate ?

mx

Author

Commented:
Things are just a lot slower. There are only about 200 records right now. But there are a lot of calculations. I'm using a lot of dlookups in other parts as well, but things got substantially slower when I added this. Yes, I have a descending index. Should I use something else to calculate age?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
It's it just this line that is slow:

dtYourDateVariable = DLookup("ADate", "tblNAPatient", "AID = AID")


If you single step the code using F8 ... when you hit F8 on this line, do you see a big delay?

mx

Author

Commented:
I have to use around 9 dlookups like to code below to find different variables to calculate BMI for age, weight for age, and height for age like the one below.
BMI4A_L: IIf([Gender]="Male",DLookUp("[L]","tblGCBMI4AgeMale","[Age_M] = " & [AgeL]),DLookUp("[L]","tblGCBMI4AgeFemale","[Age_M] = " & [AgeL]))

Open in new window

Author

Commented:
I'm not sure how to do that. The code executes in a query.
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
Of course you could use a recordset - even keep it open, but I cannot say that would necessarily be faster - you would really have to test it.

From where, and how often - do you call this function ?

mx
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007
Commented:
"code executes in a query."
So, the DLookup is being called multiple times - for each row.  Consider creating an additional query that returns 'dtYourDateVariable' - and join that to your existing query.  Then just pass this date to the Function to get the age (and removed the DLookup from the function).

One way or another, you need  to eliminate the DLookup from being called for each row in the query.  A JOIN to another query will be MUCH faster.

mx

Author

Commented:
I around 80 kids (records) that I scroll thru on my main form. Each time I scroll to a new kid this calculation is computed. Most of the other calculation have to have this to work.

Author

Commented:
The main query only execute one record at a time. The main form uses a subform when you scroll to just  pull the data for that specific kid... I'm pretty sure. It takes a 2or 3 seconds to run the query. It scrolls a lot faster than that.

Author

Commented:
I'm not sure how to write the sql code for this. Would it look like this?...

 (SELECT Age.ADate FROM qNutrtion_Assessment AS Age WHERE qNutrition_Assessment.AID=Age.AID)

Can replace this with the where the dlookup is located?

Author

Commented:
I mean... Can I place this where the current dlookup function is located?
mbizupNerd
Most Valuable Expert 2012
Top Expert 2013
Commented:
It might help mx and others in making recommendations to see a posted sample. Make sure there is no personal or otherwise sensitive data in an uploaded fille - just junk data.

It's not clear to me from this thread how your function is being called, but as mx has mentioned, it is a good idea to avoid VBA function calls in your queries as much as you can. They definitely "bog things down".
MIS Liason
Most Valuable Expert 2012
Commented:
jdallain,

...You may also be able to simply bring dtYourDateVariable into this query directly, by bringing in tblNAPatient and joining it.
(and by modifying the function slightly)

Thus avoiding Dlookup altogether.

Here is a sample:

JeffCoachman
Access-EEQ24092679DLookUpQueryAl.mdb
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sorry, I clicked "Submit" by accident...

See the query named: qryData
... and the modified function, in the above sample.

JeffCoachman
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
<<...You may also be able to simply bring dtYourDateVariable into this query directly, by bringing in tblNAPatient and joining it.
(and by modifying the function slightly)

Thus avoiding Dlookup altogether.>>
  That's the ticket.  There is no reason in the world to be using a DLookup in a query.
JimD.
BTW, I'm just seconding the idea.  Please don't accept this comment as part of the solution.

Author

Commented:
You guys and gal are the best!!!!! :o) Thank you very much! James
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
jdallain,

Great, Thanks!
;-)

Now, ... did you test it, and is it much faster?
;-)

Jeff
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Jim,

Yeah, when I read that the Syntax of an aggrgate function *is* a query:

Dlookup ("YourField",        "Your Table",          "YourCriteria")
---^------------^-------------------^-----------------------^
Select      YourField  FROM  YourTable  WHERE  YourCriteria
---^------------^-------------------^-----------------------^

...then the light came on for me!
;-)

(Sometimes I will still use Dlookup in a query if I am in a rush)
:-(

Jeff

Author

Commented:
It's faster, but I won't know how much until I test on the computers at my other job. They are a lot older and use Access 2002.  Thanks for the example Jeff! Slowly but surely, all this stuff is starting to make sense. I think I'm going to take a couple classes online at a local community college in Database Management and VB 5.0. I'm pretty sure everything will start to come into focus after that. Thanks again!

James
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
You should find it quite a bit faster<g>.
Domain functions can't be optimized in a query.  You'll also end up doing a Dlookup for every row.  This is equivalent to doing a SQL sub-query.
 All the domain functions were meant to be used in VBA code.  That's their only reason for existence.   As Jeff has pointed out so neatly, all they really are is SQL statements that are wrapped so you can use them from VBA.
JimD.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
jdallain,

"I think I'm going to take a couple classes online at a local community college in Database Management and VB 5.0."

They usually offer a class in "Database Management/Theory" and a class in MS Access.

When you say VB 5.0, what do you mean?
VB5 is over 10 years old?
Did you mean VBA?

JeffCoachman

Author

Commented:
It's VB 2005. I got a little confused there.

They offer an intoducation to database development class that it probably wouldn't hurt to take. Although introduction classes can be pretty painful if you have experience.  Here is a description of the class....

A number of powerful tools are available to help you build databases and database applications. However, if you do not apply a systematic, structured approach to the use of those tools, you will probably produce systems that fail to meet user needs. Many projects bog down or are never completed for lack of a disciplined approach to development. This course will guide you step-by-step through all the phases of a system development project to guarantee that the resulting product will not only work as it was designed to work, but also that the design truly responds to user needs.

They offer an introduction and intermediate VB 2005 class. I'm tempted to take the intermediate, but I know I probably should start with the introduction. My programming skills a pretty weak to say the least. This is the desciption for the introducation class...

Visual Basic is the most widely used programming language for creating Windows applications. Why? It's very easy to learn because it uses keywords that closely resemble the English language, while other programming languages require memorization of commands that bear no resemblance to English.

Another reason for Visual Basic's popularity is its development environment, Visual Basic 2005, which enables you to rapidly build Windows applications. Creating a Windows application ordinarily requires you to write lengthy and complex code. But Visual Basic 2005 relieves you of this task. Instead, it enables you to create the application and its components literally with the click of a button or menu item. It even writes all of the necessary code to get the application started for you. You can view and even fine-tune the code, but it spares you a lot of the grunt work.

While Visual Basic 2005 helps you write a Windows application, you still need to write code and be able to plan your application. This course will show you how. You'll learn the building blocks of programming, including using variables, control structures, and loops. You'll find out how to use the large function library built into Visual Basic 2005, including the .NET Framework, as well as how to write and use your own functions. You'll learn how to use the large and varied library of controls Windows offers. And you'll learn how to access files and handle errors. Since Windows applications are event-driven and everything in Visual Basic 2005 is treated as a programmable object, you'll also learn about event-driven and object-oriented programming, concepts important not just in Visual Basic, but also in other programming languages you may want to learn in the future.

Author

Commented:
I wanted you all to know that I got rid of all the dlookups in my queries. It's soooooooooo amazing!!!! It took my breath away. Everything is calculated almost instantaneously. It's just so amazing!!!! I can't thank everyone enough! There should be a REALLY BIG WARNING along side every domain aggregate function that states "Use of this function in a query can slow down your program considerablely!" Be well, James
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Application Developer
Top Expert 2007

Commented:
excellent!!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial