Why is this strSQL not working??? Please Help

[Brian]

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

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

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

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

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

 

[AccessVandal via AccessMonster.com]

Hi Gina,

I'm not surprise that it will work with the Query Grid.

I'm surprise that the "Dlookup" in your VBA editor did not gave you any error.


Hope it make sense to you.

It does exist. If I take the same SQL and paste it in a query it runs
perfectly.

Hi Gina,

[quoted text clipped - 20 lines]

 

[Gina Whipp]

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

ok.

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

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

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


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

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 Whipp]

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

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


Rob

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

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

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

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 Whipp]

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

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

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

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

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

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 Whipp]

It did give me an error till John pointed out my problem but even with that
fixed still too slow and not what I want. Now I've moved to Filtering

--
Gina Whipp

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

Hi Gina,

I'm not surprise that it will work with the Query Grid.

I'm surprise that the "Dlookup" in your VBA editor did not gave you any
error.


Hope it make sense to you.

It does exist. If I take the same SQL and paste it in a query it runs
perfectly.

Hi Gina,

[quoted text clipped - 20 lines]

INNER JOIN tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID"

 

[Brian]

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

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

ok.

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


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

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


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

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]

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

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

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


Rob

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

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

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

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

 

[Guest]

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.)

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 Whipp]

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

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

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

ok.

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


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

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


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

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 Whipp]

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

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

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

ok.

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


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

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


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

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

 

[Mattias Jonsson]

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:

 

[Gina Whipp]

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.

 

[Guest]

My apologies for butting in........

 

[Gina Whipp]

No apologies needed... can take all the ideas I can get!!!

 

[Brian]

Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

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

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

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


ok.

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


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

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


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

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 Whipp]

Query still unupdateable

--
Gina Whipp

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

Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

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

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

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


ok.

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


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

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


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

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]

Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID to
tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you wanted
it to update.

Brian

Query still unupdateable

--
Gina Whipp

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

Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

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


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

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


ok.

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


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

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


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

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 Whipp]

Brian,

You missed one fo my responses ( I've typed so, I feel like I missed
one of my responses!!!) btTerritoryStateID is NOT a primary key or this
thing would have worked ages ago.

I got this whole thing to work by making the query Inconsistent Updates.
Not my first choice but after trying to convince the end-user to let me set
up the form. (In one of my former replies I stated that he insisted on
doing the forms.)

tblBrokerTerritory.btTerritoryStateID is the NOT primary key of
tblBrokerTerritory

Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID to
tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you
wanted it to update.

Brian

Query still unupdateable

--
Gina Whipp

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

Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

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


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

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


ok.

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


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

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


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

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]

Oh, ok, well if you got it working that good.
Brian

Brian,

You missed one fo my responses ( I've typed so, I feel like I missed
one of my responses!!!) btTerritoryStateID is NOT a primary key or this
thing would have worked ages ago.

I got this whole thing to work by making the query Inconsistent Updates.
Not my first choice but after trying to convince the end-user to let me
set up the form. (In one of my former replies I stated that he insisted
on doing the forms.)

tblBrokerTerritory.btTerritoryStateID is the NOT primary key of
tblBrokerTerritory

Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID
to tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you
wanted it to update.

Brian

Query still unupdateable

--
Gina Whipp

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


Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

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


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

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


ok.

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


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

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


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

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