here you go...

SELECT tblLister.lListerID,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount,
tblListerListing.llTerritoryName, tblListerListing.llStateID
FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON
tblLister.lListerID = tblListerListing.llListerID) ON
tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID;


--
Gina Whipp

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


"Brian" s@y wrote in message ...
ok.

"Gina Whipp" wrote in message
...
Yes I tried... you want to see the SQL? Maybe I did something wrong...

--
Gina Whipp

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


"Brian" s@y wrote in message
...
I don't know your relationships for sure but once you include the primary
keys it looks like it should be updateable. The subquery should work
always, did you try it?
Brian

"Gina Whipp" wrote in message
...
Yes they are slow but trying to an inner join makes the query
UNupdateable. Ibelieve I may start over again and try another way...
--
Gina Whipp

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


"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