Gina

Your closing quote is quite apt...

I didn't see what is happening when you leave the DLookup in.

Are you getting an error message? If so, what?

Are you getting results, but the WRONG results? If so, describe.

You haven't described the underlying table structure. Is there a chance you
could use a query and join on the value you are using in the DLookup?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"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