Why does my dlookup take so long?

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.

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?

Try using

Forms!MyFormName!MyDateFieldName

... instead of dlookup to reference your date field

Ok, I'll try that tomorrow morning when I get in.

Stay tuned...

For some working examples see: Report Dialog Examples

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

 

<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...

JeffCoachman

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.  

pde,

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.  

Also,

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
FROM BILMAC_MBRLOG INNER JOIN BILMAC_MBRMSTR ON BILMAC_MBRLOG.[MLSS#] = BILMAC_MBRMSTR.[MBSS#]
WHERE (((BILMAC_MBRLOG.MLFILE)="MBRMST") AND ((BILMAC_MBRLOG.MLFLD)="MBFTPT" Or (BILMAC_MBRLOG.MLFLD)="MBSTOR") AND ((BILMAC_MBRLOG.MLOLCO)<>"000000" And (BILMAC_MBRLOG.MLOLCO)<>"000" And (BILMAC_MBRLOG.MLOLCO)<>"") AND ((BILMAC_MBRLOG.MLNUCO)<>"002") AND ((BILMAC_MBRLOG.MLCHDT)=[Forms]![RUNDTEf]![CONVERTED_DATE]) AND ((Left([MLOLCO],3))<>Left([MLNUCO],3)))
ORDER BY BILMAC_MBRLOG.[MLSS#];

Select allOpen in new window

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.