If your back-end is SQLServer then you definitely do NOT want dlookup in
your queries, in fact any function (even IIF) that is VBA and not directly
supported by SQLServer is going to take a long time because Access is going
to download all the data and then execute the function.

You can get things much faster by also binding your form to an ADO recordset
that is opened on a connection directly to the backend instead of going
though linked tables.

Brian

"Gina Whipp" wrote in message
...
Robert,

I am having this problem in Access 2003 and the problem is opening a form
based on the contents of a log-on form. I want the records filtered to
whatever is the Broker's territory. I now have the form opening based on
a filter but it drags to open. I'm still working on a better way. I
should mention the back-end is on a SQL server. I may have to test the
opening of the form that way but for now it's toooooo slow.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II


"Robert Morley" wrote in message
...
I've had experiences with Access where subqueries caused lockups in the
query designer. It's been a few years, so I don't remember specifics,
but I remember ultimately HAVING to write two separate queries, because
the subquery was just causing major problems. It's been long enough, I
couldn't even tell you which version of Access I was using, but probably
A2K. It might have been something version-specific or even
query-specific, but in the end, I avoided subqueries entirely until I
switched to SQL Server.


Rob

"Brian" s@y wrote in message
...
You should try to avoid those 'D' functions in your queries since they
are very slow.
Why don'y you try using a subquery instead...
(SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID =
tblListerListing.llStateID) AS BrokerID
or just do another inner join on tblBrokerTerritory.btTerritoryStateID =
tblListerListing.llStateID
Brian

"Gina Whipp" wrote in message
...
If I take out the Dlookup part all is well but I really need that
Dlookup. Can someone explian why?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount,
tblListerListing.llStateID, tblListerListing.llTerritoryName FROM
tblLister INNER JOIN tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID"

I use the above because of the below:

If Not Me.cboListTypeID = "" Then
Select Case cboListTypeID
Case "A"
strFilterSQL = strSQL
Me.RecordSource = strFilterSQL
Case "M"
strFilterSQL = strSQL & " Where [BrokerID] =
Forms![frmLogOn]![cboLoginID];"
Me.Form.AllowAdditions = False
Me.RecordSource = strFilterSQL
Case Else
strFilterSQL = strSQL & " Where [llListingTypeID] = '" &
cboListTypeID & "';"
Me.RecordSource = strFilterSQL
End Select
End If

Thanks to anyone who can help,
Gina Whipp

"I feel I have been denied critical, need to know, information!" -
Tremors II