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 |
#1
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 |
#2
|
|||
|
|||
Why is this strSQL not working??? Please Help
Gina
Your closing quote is quite apt... I didn't see what is happening when you leave the DLookup in. Are you getting an error message? If so, what? Are you getting results, but the WRONG results? If so, describe. You haven't described the underlying table structure. Is there a chance you could use a query and join on the value you are using in the DLookup? Regards Jeff Boyce Microsoft Office/Access MVP "Gina Whipp" wrote in message ... If I take out the Dlookup part all is well but I really need that Dlookup. Can someone explian why? Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName, tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber, tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber, tblLister.lPhoneNumber, DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" & [llStateID] & "'") AS BrokerID, tblListerListing.llListingID, tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold, tblListerListing.llInvestmentPotential, tblListerListing.llListingTypeID, tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount, tblListerListing.llStateID, tblListerListing.llTerritoryName FROM tblLister INNER JOIN tblListerListing ON tblLister.lListerID = tblListerListing.llListerID" I use the above because of the below: If Not Me.cboListTypeID = "" Then Select Case cboListTypeID Case "A" strFilterSQL = strSQL Me.RecordSource = strFilterSQL Case "M" strFilterSQL = strSQL & " Where [BrokerID] = Forms![frmLogOn]![cboLoginID];" Me.Form.AllowAdditions = False Me.RecordSource = strFilterSQL Case Else strFilterSQL = strSQL & " Where [llListingTypeID] = '" & cboListTypeID & "';" Me.RecordSource = strFilterSQL End Select End If Thanks to anyone who can help, Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II |
#3
|
|||
|
|||
Why is this strSQL not working??? Please Help
On Mon, 26 Feb 2007 16:26:18 -0500, Gina Whipp wrote:
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 If the only problem is with the DLookUp, what is the datatype of the [btTerritoryStateID] field? If it is text, then your syntax look OK. However, if it is a Number datatype, then you would use: DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]=" & [llStateID]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#4
|
|||
|
|||
Why is this strSQL not working??? Please Help
Is [btTerritoryStateID] in the DLookup a String value or a Numeric value?
The syntax you used is for a String value. If it is Numeric, try: DLookUp( "btBrokerID", "tblBrokerTerritory", "[btTerritoryStateID] = " & tblListerListing.llStateID ) -- HTH Van T. Dinh MVP (Access) "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 |
#5
|
|||
|
|||
Why is this strSQL not working??? Please Help
Since you are building this as string, you need to double the quotation
marks ...., DLookUp(""btBrokerID"",""tblBrokerTerritory"",""bt TerritoryStateID='"" & llStateID & ""'"") AS BrokerID ... If you use debug.Print strSQL You will see your version ends up as DLookUp(btBrokerID,tblBrokerTerritory,[btTerritoryStateID]=' & [llStateID] & ') AS BrokerID Instead of the following DLookUp("btBrokerID","tblBrokerTerritory","btTerri toryStateID='" & llStateID & "'") AS BrokerID ... '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Gina Whipp wrote: 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 |
#6
|
|||
|
|||
Why is this strSQL not working??? Please Help
It's not a number...
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "fredg" wrote in message ... On Mon, 26 Feb 2007 16:26:18 -0500, Gina Whipp wrote: 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 If the only problem is with the DLookUp, what is the datatype of the [btTerritoryStateID] field? If it is text, then your syntax look OK. However, if it is a Number datatype, then you would use: DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]=" & [llStateID]) -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#7
|
|||
|
|||
Why is this strSQL not working??? Please Help
Not numeric it's text
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "Van T. Dinh" wrote in message ... Is [btTerritoryStateID] in the DLookup a String value or a Numeric value? The syntax you used is for a String value. If it is Numeric, try: DLookUp( "btBrokerID", "tblBrokerTerritory", "[btTerritoryStateID] = " & tblListerListing.llStateID ) -- HTH Van T. Dinh MVP (Access) "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 |
#8
|
|||
|
|||
Why is this strSQL not working??? Please Help
John... that was it but now the other part won't work.... Why doesn't it
recognize BrokerID, it keeps asking for parameter value. Big THANKS 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 -- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "John Spencer" wrote in message ... Since you are building this as string, you need to double the quotation marks ..., DLookUp(""btBrokerID"",""tblBrokerTerritory"",""bt TerritoryStateID='"" & llStateID & ""'"") AS BrokerID ... If you use debug.Print strSQL You will see your version ends up as DLookUp(btBrokerID,tblBrokerTerritory,[btTerritoryStateID]=' & [llStateID] & ') AS BrokerID Instead of the following DLookUp("btBrokerID","tblBrokerTerritory","btTerri toryStateID='" & llStateID & "'") AS BrokerID ... '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Gina Whipp wrote: 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 |
#9
|
|||
|
|||
Why is this strSQL not working??? Please Help
Debug.Print strSQL produces nothing... it's blank, not sure what to do now
-- Gina Whipp "I feel I have been denied critical, need to know, information!" - Tremors II "John Spencer" wrote in message ... Since you are building this as string, you need to double the quotation marks ..., DLookUp(""btBrokerID"",""tblBrokerTerritory"",""bt TerritoryStateID='"" & llStateID & ""'"") AS BrokerID ... If you use debug.Print strSQL You will see your version ends up as DLookUp(btBrokerID,tblBrokerTerritory,[btTerritoryStateID]=' & [llStateID] & ') AS BrokerID Instead of the following DLookUp("btBrokerID","tblBrokerTerritory","btTerri toryStateID='" & llStateID & "'") AS BrokerID ... '================================================= === John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '================================================= === Gina Whipp wrote: 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 |
#10
|
|||
|
|||
Why is this strSQL not working??? Please Help
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 | |
|
|