If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#12
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#13
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#14
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#15
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#16
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#17
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#18
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#19
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#20
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
Thread Tools | |
Display Modes | |
|
|