Why is this strSQL not working??? Please Help

Well, I'm having it now.. all service packs applied

--
Gina Whipp

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


"Robert Morley" wrote in message
...
Could've been. It was several years ago, so could well have been a bug
that wasn't addressed at the time.


Rob

"Brian" s@y wrote in message
...
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

Already figured out don't want the DLookUp but all alternate methods not
working either....

--
Gina Whipp

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


"Brian" s@y wrote in message
...
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

No don't do both, if you do the inner join btBrokerID will be a field you
can select straight off
SELECT tblLister.lListerID,
btBrokerID 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;
(I'm assuming that your join is correct for the relationship)

OR with the subquery
SELECT tblLister.lListerID,
tblLister.lFirstName,
tblLister.lLastName,
tblLister.leMailAddress,
tblLister.lFaxNumber,
tblLister.lCellPhoneNumber,
tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
(SELECT btBrokerID FROM tblBrokerTerritory WHERE
tblBrokerTerritory.btTerritoryStateID = tblListerListing.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

"Gina Whipp" wrote in message
...
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

You mean the query designer is crashing or is it that it's just sloooow?
Brian

"Gina Whipp" wrote in message
...
Well, I'm having it now.. all service packs applied

--
Gina Whipp

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


"Robert Morley" wrote in message
...
Could've been. It was several years ago, so could well have been a bug
that wasn't addressed at the time.


Rob

"Brian" s@y wrote in message
...
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

Hi Gina,

AFAIK, you can't embed a VBA function inside a string and have it return a
value.

Try the following (I broke it up for ease of reading):


Const strSQL = "SELECT"
strSQL = strSQL & " tblLister.lListerID,"
strSQL = strSQL & " tblLister.lFirstName,"
strSQL = strSQL & " tblLister.lLastName,"
strSQL = strSQL & " tblLister.leMailAddress,"
strSQL = strSQL & " tblLister.lFaxNumber,"
strSQL = strSQL & " tblLister.lCellPhoneNumber,"
strSQL = strSQL & " tblLister.lOtherPhoneNumber,"
strSQL = strSQL & " tblLister.lPhoneNumber, " &

DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'")

& " AS BrokerID,"
strSQL = strSQL & " tblListerListing.llListingID,"
strSQL = strSQL & " tblListerListing.llSaleAmount,"
strSQL = strSQL & " tblListerListing.llInActiveOrSold,"
strSQL = strSQL & " tblListerListing.llInvestmentPotential,"
strSQL = strSQL & " tblListerListing.llListingTypeID,"
strSQL = strSQL & " tblListerListing.llTerritoryPopulation,"
strSQL = strSQL & " tblListerListing.llMonthlyRent,"
strSQL = strSQL & " tblListerListing.llMonthlyRevenue,"
strSQL = strSQL & " tblListerListing.llAskingPrice,"
strSQL = strSQL & " tblListerListing.llGrossSDEPercent,"
strSQL = strSQL & " tblListerListing.llGrossSDEAmount,"
strSQL = strSQL & " tblListerListing.llStateID,"
strSQL = strSQL & " tblListerListing.llTerritoryName"

strSQL = strSQL & " FROM tblLister"
strSQL = strSQL & " INNER JOIN tblListerListing "
strSQL = strSQL & " ON tblLister.lListerID = tblListerListing.llListerID;"

Debug.Print SQL


In the immediate window, the field "BrokerID" should look something like:

....,
5 as BrokerID,
.....,

(I can't tell if the return value from the DLOOKUP() is a number or a string.)


Another way might be to set a variable = to the DLOOKUP(), then build the
SQL string using the variable.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


"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

Brian,

I agree. I think the real problem is the person I am doing this for INSISTS
on designing the forms and putting fields on the form that is causing me to
do it this way and I KNOW it won't work and getting it to work is causing me
this nightmare. I believe I'm going to create a form the way I know it
will work and go from there...
--
Gina Whipp

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


"Brian" s@y wrote in message ...
No don't do both, if you do the inner join btBrokerID will be a field you
can select straight off
SELECT tblLister.lListerID,
btBrokerID 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;
(I'm assuming that your join is correct for the relationship)

OR with the subquery
SELECT tblLister.lListerID,
tblLister.lFirstName,
tblLister.lLastName,
tblLister.leMailAddress,
tblLister.lFaxNumber,
tblLister.lCellPhoneNumber,
tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
(SELECT btBrokerID FROM tblBrokerTerritory WHERE
tblBrokerTerritory.btTerritoryStateID = tblListerListing.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

"Gina Whipp" wrote in message
...
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

Brian,

Neither of those work and both StateID are foreign keys linked to a
tblStates table. Are you saying I need to turn btTerritoryStateID into a
Primary key?

--
Gina Whipp

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


"Brian" s@y wrote in message ...
No don't do both, if you do the inner join btBrokerID will be a field you
can select straight off
SELECT tblLister.lListerID,
btBrokerID 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;
(I'm assuming that your join is correct for the relationship)

OR with the subquery
SELECT tblLister.lListerID,
tblLister.lFirstName,
tblLister.lLastName,
tblLister.leMailAddress,
tblLister.lFaxNumber,
tblLister.lCellPhoneNumber,
tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
(SELECT btBrokerID FROM tblBrokerTerritory WHERE
tblBrokerTerritory.btTerritoryStateID = tblListerListing.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

"Gina Whipp" wrote in message
...
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

Gina,
Maybe you can instruct the person who is making the form in how to use
DLookup as ControlSource, instead of you putting it in the RecordSource?

Good luck,
Mattias

From: Gina Whipp, on 2/27/2007 8:51 AM:
Brian,

I agree. I think the real problem is the person I am doing this for INSISTS
on designing the forms and putting fields on the form that is causing me to
do it this way and I KNOW it won't work and getting it to work is causing me
this nightmare. I believe I'm going to create a form the way I know it
will work and go from there...

Mattias... Ummm, he just knows how to move stuff around on the form. He
doesn't really understand the theory of one-to-many, normalization, etc.. he
just knows what he wants to see on the forms and he puts it there. Up to me
to make it work. (All this so he doesn't have to pay for me to design the
forms which he's actually paying more because I'm not designing them and
have to make what he does work.) The other problem is the Dlookup on the
form doesn't run correctly, I tried that. However, changing the query to
Inconsistent Updates does work. This is not my first choice but it's
working.

--
Gina Whipp

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


"Mattias Jonsson" wrote in message
...
Gina,
Maybe you can instruct the person who is making the form in how to use
DLookup as ControlSource, instead of you putting it in the RecordSource?

Good luck,
Mattias

From: Gina Whipp, on 2/27/2007 8:51 AM:
Brian,

I agree. I think the real problem is the person I am doing this for
INSISTS on designing the forms and putting fields on the form that is
causing me to do it this way and I KNOW it won't work and getting it to
work is causing me this nightmare. I believe I'm going to create a form
the way I know it will work and go from there...

My apologies for butting in........
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)