Why does my dlookup take so long?

I have an access form that has three values on it, a date picker, a spec number, and an unbound text box.  These values reference a table, RUNDTE.  There are only three values in the RUNDTE table.

When I go to use the converted value (from the unbound text box) which is written to my RUNDTE table, as a DLOOKUP- my query never completes if I use it for a criteria expression.

Here is what I had.  DLookUp("[CONVERTED]","RUNDTE","SET=1")

There is only one row with three columns in RUNDTE and I don't understand why it won't complete the query.  Can someone please help me or offer me another suggestion?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Well, DLookup's are notoriously slow when used in a query.  Instead, try using a JOIN between the two tables.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
It'd be helpful to see the full SQL statement.

As MX notes above, you should try to avoid using domain aggregate functions in SQL statements.
Anthony6890Author Commented:
Yeah, I've been reading about how dlookup isn't the best idea.  Let me give you some background on what I'm trying to accomplish.  

On my form, I need to select a date.  That date then gets converted into CYYMMDD format in an unbound textbox.  I have various queries, that generate reports that run based off of this converted date.  I originally though it would be a good idea use the dlookup to reference the value in the unbound text box, but that's proving to be wrong.

I need this to be as user friendly as possible, which is why I created the form.  Can you guys offer me any other suggestions?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Try using


... instead of dlookup to reference your date field
Anthony6890Author Commented:
Ok, I'll try that tomorrow morning when I get in.

Stay tuned...
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPDesigner and DeveloperCommented:
For some working examples see: Report Dialog Examples

FWIW: I use a sub query not a DLookup in a query.

Jeffrey CoachmanMIS LiasonCommented:
<Why does my dlookup take so long?>

<my query never completes>

So what is the real issue here?
First you say it is slow, then you say it *Never* completes?

Dlookup may be slow if it has to search a lot of records...

But if it *Never* completes, then this ma be another issue...

Please note that you have not told us a lot about the purpose or design of this form, only how you designed it...
(Converted dates in unbound textbox use as query criteria...?)

Perhaps your system is having a tough time "Converting" the dates?
Perhaps it is an issue with the double quotes...

Perhaps if we knew the purpose and design, we could perhaps suggest an alternative approach...

Anthony6890Author Commented:
I say that it never completes, because after waiting 10 minutes the query isn't done.  The computer doesn't freeze, the status bar at the bottom of Access 2007 stays green, but it doesn't ever fully complete.  

The purpose of this form is to allow a user to select a date from a date picker, then that date is converted into the CYYMMDD format in an unbound text box.  Before the form closes, it writes to a table that updates the row consisting of the date picked, a fixed detail set at 1, and the converted date.  At one time there is never more than one row in my RUNDTE table because the data is just overlaid each time a new date is selected.  

The converted date in CYYMMDD format needs to be used to run multiple queries via an ODBC connection to an iSeries.  It is a key criteria and offers an easy way of using dates rather than going into each query to alter each individual date.  

If you can think of another approach, I would be open to trying it.  I will be trying pde's suggestion when I get in tomorrow.  
Anthony6890Author Commented:

When I tried your forms suggestion this morning, it's asking me for the value as a parameter when I execute the query.  I would assume it's because the form isn't open.  
Anthony6890Author Commented:

here's the full sql
SELECT BILMAC_MBRLOG.[MLSS#], BILMAC_MBRLOG.MLPRNA, BILMAC_MBRLOG.MLUSER, BILMAC_MBRLOG.MLFILE, BILMAC_MBRLOG.MLFLD, BILMAC_MBRLOG.MLOLCO, BILMAC_MBRLOG.MLNUCO, BILMAC_MBRLOG.MLCHDT, CDate(IIf(Len([MBHIRE])=7,Format(Mid([MBHIRE],4,2) & "-" & Mid([MBHIRE],6,2) & "-" & Mid([MBHIRE],2,2),"mm/dd/yyyy"),Format(Mid([MBHIRE],3,2) & "-" & Mid([MBHIRE],5,2) & "-" & Left([MBHIRE],2),"mm/dd/yyyy"))) AS HIRE_DATE, CDate(IIf(Len([MBELDT])=7,Format(Mid([MBELDT],4,2) & "-" & Mid([MBELDT],6,2) & "-" & Mid([MBELDT],2,2),"mm/dd/yyyy"),Format(Mid([MBELDT],3,2) & "-" & Mid([MBELDT],5,2) & "-" & Left([MBELDT],2),"mm/dd/yyyy"))) AS ELIG_DATE

Open in new window

Anthony6890Author Commented:
Hey everyone I was able to figure it out.  I used MX's first suggestion of doing an inner join.  It worked like a charm.

Thanks again everyone for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.