I've never had that problem and I use them all the time, especially if the
db might be upsized later on.
You probably needed a service pack or something.
Brian

"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