A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Why is this strSQL not working??? Please Help



 
 
Thread Tools Display Modes
  #1  
Old February 26th, 2007, 10:26 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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  
Old February 26th, 2007, 10:39 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default 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  
Old February 26th, 2007, 11:03 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
fredg
external usenet poster
 
Posts: 4,386
Default 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  
Old February 26th, 2007, 11:04 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Van T. Dinh
external usenet poster
 
Posts: 1,048
Default 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  
Old February 26th, 2007, 11:27 PM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
John Spencer
external usenet poster
 
Posts: 2,364
Default 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  
Old February 27th, 2007, 12:18 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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  
Old February 27th, 2007, 12:18 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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  
Old February 27th, 2007, 12:27 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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  
Old February 27th, 2007, 12:47 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Gina Whipp
external usenet poster
 
Posts: 3,500
Default 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  
Old February 27th, 2007, 01:26 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
Brian
external usenet poster
 
Posts: 34
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.