jdallain
asked on
Something faster than dlookup
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
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
How many records are in the table?
Do you have an Index on ADate ?
mx
Do you have an Index on ADate ?
mx
ASKER
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?
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
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
ASKER
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]))
ASKER
I'm not sure how to do that. The code executes in a query.
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
From where, and how often - do you call this function ?
mx
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
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.
ASKER
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=
Can replace this with the where the dlookup is located?
(SELECT Age.ADate FROM qNutrtion_Assessment AS Age WHERE qNutrition_Assessment.AID=
Can replace this with the where the dlookup is located?
ASKER
I mean... Can I place this where the current dlookup function is located?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, I clicked "Submit" by accident...
See the query named: qryData
... and the modified function, in the above sample.
JeffCoachman
See the query named: qryData
... and the modified function, in the above sample.
JeffCoachman
<<...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.
(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.
ASKER
You guys and gal are the best!!!!! :o) Thank you very much! James
jdallain,
Great, Thanks!
;-)
Now, ... did you test it, and is it much faster?
;-)
Jeff
Great, Thanks!
;-)
Now, ... did you test it, and is it much faster?
;-)
Jeff
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
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
ASKER
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
James
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.
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.
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
"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
ASKER
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.
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.
ASKER
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
mx