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 |
#21
|
|||
|
|||
Why is this strSQL not working??? Please Help
Well, I'm having it now.. all service packs applied
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Robert Morley" wrote in message ... Could've been. It was several years ago, so could well have been a bug that wasn't addressed at the time. Rob "Brian" s@y wrote in message ... I've never had that problem and I use them all the time, especially if the db might be upsized later on. You probably needed a service pack or something. Brian "Robert Morley" wrote in message ... I've had experiences with Access where subqueries caused lockups in the query designer. It's been a few years, so I don't remember specifics, but I remember ultimately HAVING to write two separate queries, because the subquery was just causing major problems. It's been long enough, I couldn't even tell you which version of Access I was using, but probably A2K. It might have been something version-specific or even query-specific, but in the end, I avoided subqueries entirely until I switched to SQL Server. Rob "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#22
|
|||
|
|||
Why is this strSQL not working??? Please Help
Already figured out don't want the DLookUp but all alternate methods not
working either.... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... If your back-end is SQLServer then you definitely do NOT want dlookup in your queries, in fact any function (even IIF) that is VBA and not directly supported by SQLServer is going to take a long time because Access is going to download all the data and then execute the function. You can get things much faster by also binding your form to an ADO recordset that is opened on a connection directly to the backend instead of going though linked tables. Brian "Gina Whipp" wrote in message ... Robert, I am having this problem in Access 2003 and the problem is opening a form based on the contents of a log-on form. I want the records filtered to whatever is the Broker's territory. I now have the form opening based on a filter but it drags to open. I'm still working on a better way. I should mention the back-end is on a SQL server. I may have to test the opening of the form that way but for now it's toooooo slow. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Robert Morley" wrote in message ... I've had experiences with Access where subqueries caused lockups in the query designer. It's been a few years, so I don't remember specifics, but I remember ultimately HAVING to write two separate queries, because the subquery was just causing major problems. It's been long enough, I couldn't even tell you which version of Access I was using, but probably A2K. It might have been something version-specific or even query-specific, but in the end, I avoided subqueries entirely until I switched to SQL Server. Rob "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#23
|
|||
|
|||
Why is this strSQL not working??? Please Help
No don't do both, if you do the inner join btBrokerID will be a field you
can select straight off SELECT tblLister.lListerID, btBrokerID AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; (I'm assuming that your join is correct for the relationship) OR with the subquery SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, (SELECT btBrokerID FROM tblBrokerTerritory WHERE tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID) AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID "Gina Whipp" wrote in message ... here you go... SELECT tblLister.lListerID, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... ok. "Gina Whipp" wrote in message ... Yes I tried... you want to see the SQL? Maybe I did something wrong... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... I don't know your relationships for sure but once you include the primary keys it looks like it should be updateable. The subquery should work always, did you try it? Brian "Gina Whipp" wrote in message ... Yes they are slow but trying to an inner join makes the query UNupdateable. Ibelieve I may start over again and try another way... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#24
|
|||
|
|||
Why is this strSQL not working??? Please Help
You mean the query designer is crashing or is it that it's just sloooow?
Brian "Gina Whipp" wrote in message ... Well, I'm having it now.. all service packs applied -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Robert Morley" wrote in message ... Could've been. It was several years ago, so could well have been a bug that wasn't addressed at the time. Rob "Brian" s@y wrote in message ... I've never had that problem and I use them all the time, especially if the db might be upsized later on. You probably needed a service pack or something. Brian "Robert Morley" wrote in message ... I've had experiences with Access where subqueries caused lockups in the query designer. It's been a few years, so I don't remember specifics, but I remember ultimately HAVING to write two separate queries, because the subquery was just causing major problems. It's been long enough, I couldn't even tell you which version of Access I was using, but probably A2K. It might have been something version-specific or even query-specific, but in the end, I avoided subqueries entirely until I switched to SQL Server. Rob "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#25
|
|||
|
|||
Why is this strSQL not working??? Please Help
Hi Gina,
AFAIK, you can't embed a VBA function inside a string and have it return a value. Try the following (I broke it up for ease of reading): Const strSQL = "SELECT" strSQL = strSQL & " tblLister.lListerID," strSQL = strSQL & " tblLister.lFirstName," strSQL = strSQL & " tblLister.lLastName," strSQL = strSQL & " tblLister.leMailAddress," strSQL = strSQL & " tblLister.lFaxNumber," strSQL = strSQL & " tblLister.lCellPhoneNumber," strSQL = strSQL & " tblLister.lOtherPhoneNumber," strSQL = strSQL & " tblLister.lPhoneNumber, " & DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") & " AS BrokerID," strSQL = strSQL & " tblListerListing.llListingID," strSQL = strSQL & " tblListerListing.llSaleAmount," strSQL = strSQL & " tblListerListing.llInActiveOrSold," strSQL = strSQL & " tblListerListing.llInvestmentPotential," strSQL = strSQL & " tblListerListing.llListingTypeID," strSQL = strSQL & " tblListerListing.llTerritoryPopulation," strSQL = strSQL & " tblListerListing.llMonthlyRent," strSQL = strSQL & " tblListerListing.llMonthlyRevenue," strSQL = strSQL & " tblListerListing.llAskingPrice," strSQL = strSQL & " tblListerListing.llGrossSDEPercent," strSQL = strSQL & " tblListerListing.llGrossSDEAmount," strSQL = strSQL & " tblListerListing.llStateID," strSQL = strSQL & " tblListerListing.llTerritoryName" strSQL = strSQL & " FROM tblLister" strSQL = strSQL & " INNER JOIN tblListerListing " strSQL = strSQL & " ON tblLister.lListerID = tblListerListing.llListerID;" Debug.Print SQL In the immediate window, the field "BrokerID" should look something like: ...., 5 as BrokerID, ....., (I can't tell if the return value from the DLOOKUP() is a number or a string.) Another way might be to set a variable = to the DLOOKUP(), then build the SQL string using the variable. HTH -- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#26
|
|||
|
|||
Why is this strSQL not working??? Please Help
Brian,
I agree. I think the real problem is the person I am doing this for INSISTS on designing the forms and putting fields on the form that is causing me to do it this way and I KNOW it won't work and getting it to work is causing me this nightmare. I believe I'm going to create a form the way I know it will work and go from there... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... No don't do both, if you do the inner join btBrokerID will be a field you can select straight off SELECT tblLister.lListerID, btBrokerID AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; (I'm assuming that your join is correct for the relationship) OR with the subquery SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, (SELECT btBrokerID FROM tblBrokerTerritory WHERE tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID) AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID "Gina Whipp" wrote in message ... here you go... SELECT tblLister.lListerID, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... ok. "Gina Whipp" wrote in message ... Yes I tried... you want to see the SQL? Maybe I did something wrong... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... I don't know your relationships for sure but once you include the primary keys it looks like it should be updateable. The subquery should work always, did you try it? Brian "Gina Whipp" wrote in message ... Yes they are slow but trying to an inner join makes the query UNupdateable. Ibelieve I may start over again and try another way... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#27
|
|||
|
|||
Why is this strSQL not working??? Please Help
Brian,
Neither of those work and both StateID are foreign keys linked to a tblStates table. Are you saying I need to turn btTerritoryStateID into a Primary key? -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... No don't do both, if you do the inner join btBrokerID will be a field you can select straight off SELECT tblLister.lListerID, btBrokerID AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; (I'm assuming that your join is correct for the relationship) OR with the subquery SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, (SELECT btBrokerID FROM tblBrokerTerritory WHERE tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID) AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID "Gina Whipp" wrote in message ... here you go... SELECT tblLister.lListerID, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llListingTypeID, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llTerritoryName, tblListerListing.llStateID FROM tblBrokerTerritory INNER JOIN (tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID) ON tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID; -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... ok. "Gina Whipp" wrote in message ... Yes I tried... you want to see the SQL? Maybe I did something wrong... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... I don't know your relationships for sure but once you include the primary keys it looks like it should be updateable. The subquery should work always, did you try it? Brian "Gina Whipp" wrote in message ... Yes they are slow but trying to an inner join makes the query UNupdateable. Ibelieve I may start over again and try another way... -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Brian" s@y wrote in message ... You should try to avoid those 'D' functions in your queries since they are very slow. Why don'y you try using a subquery instead... (SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID = tblListerListing.llStateID) AS BrokerID or just do another inner join on tblBrokerTerritory.btTerritoryStateID = tblListerListing.llStateID Brian "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#28
|
|||
|
|||
Why is this strSQL not working??? Please Help
Gina,
Maybe you can instruct the person who is making the form in how to use DLookup as ControlSource, instead of you putting it in the RecordSource? Good luck, Mattias From: Gina Whipp, on 2/27/2007 8:51 AM: Brian, I agree. I think the real problem is the person I am doing this for INSISTS on designing the forms and putting fields on the form that is causing me to do it this way and I KNOW it won't work and getting it to work is causing me this nightmare. I believe I'm going to create a form the way I know it will work and go from there... |
#29
|
|||
|
|||
Why is this strSQL not working??? Please Help
Mattias... Ummm, he just knows how to move stuff around on the form. He
doesn't really understand the theory of one-to-many, normalization, etc.. he just knows what he wants to see on the forms and he puts it there. Up to me to make it work. (All this so he doesn't have to pay for me to design the forms which he's actually paying more because I'm not designing them and have to make what he does work.) The other problem is the Dlookup on the form doesn't run correctly, I tried that. However, changing the query to Inconsistent Updates does work. This is not my first choice but it's working. -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Mattias Jonsson" wrote in message ... Gina, Maybe you can instruct the person who is making the form in how to use DLookup as ControlSource, instead of you putting it in the RecordSource? Good luck, Mattias From: Gina Whipp, on 2/27/2007 8:51 AM: Brian, I agree. I think the real problem is the person I am doing this for INSISTS on designing the forms and putting fields on the form that is causing me to do it this way and I KNOW it won't work and getting it to work is causing me this nightmare. I believe I'm going to create a form the way I know it will work and go from there... |
#30
|
|||
|
|||
Why is this strSQL not working??? Please Help
My apologies for butting in........
-- Steve S -------------------------------- "Veni, Vidi, Velcro" (I came; I saw; I stuck around.) |
Thread Tools | |
Display Modes | |
|
|