Why is this strSQL not working??? Please Help

G

Gina Whipp

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
 
J

Jeff Boyce

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
 
F

fredg

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
Click to expand...

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])
 
V

Van T. Dinh

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

John Spencer

Since you are building this as string, you need to double the quotation
marks

....,
DLookUp(""btBrokerID"",""tblBrokerTerritory"",""btTerritoryStateID='"" &
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","btTerritoryStateID='" &
llStateID & "'") AS BrokerID ...


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
G

Gina Whipp

It's not a number...

--
Gina Whipp

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


fredg said:
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
Click to expand...

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])
Click to expand...
 
G

Gina Whipp

Not numeric it's text

--
Gina Whipp

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


Van T. Dinh said:
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 said:
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
Click to expand...
Click to expand...
 
G

Gina Whipp

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 said:
Since you are building this as string, you need to double the quotation
marks

...,
DLookUp(""btBrokerID"",""tblBrokerTerritory"",""btTerritoryStateID='"" &
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","btTerritoryStateID='" &
llStateID & "'") AS BrokerID ...


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Gina said:
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
Click to expand...
Click to expand...
 
G

Gina Whipp

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 said:
Since you are building this as string, you need to double the quotation
marks

...,
DLookUp(""btBrokerID"",""tblBrokerTerritory"",""btTerritoryStateID='"" &
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","btTerritoryStateID='" &
llStateID & "'") AS BrokerID ...


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Gina said:
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
Click to expand...
Click to expand...
 
B

Brian

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
 
G

Gina Whipp

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 said:
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 said:
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
Click to expand...
Click to expand...
 
A

AccessVandal via AccessMonster.com

Hi Gina,

According to your SQL syntax, "tblBrokerTerritory" does not exist.
That means your Dlookup can't find it.
I can't tell much about your relationship of your tables.


Gina said:
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"
Click to expand...
 
G

Gina Whipp

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

--
Gina Whipp

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


AccessVandal via AccessMonster.com said:
Hi Gina,

According to your SQL syntax, "tblBrokerTerritory" does not exist.
That means your Dlookup can't find it.
I can't tell much about your relationship of your tables.


Gina said:
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"
Click to expand...
Click to expand...
 
R

Robert Morley

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 said:
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 said:
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
Click to expand...
Click to expand...
 
B

Brian

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Brian

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
G

Gina Whipp

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 said:
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 said:
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 said:
You should try to avoid those 'D' functions in your queries since they
are very slow.
Why don'y you try using a subquery instead...
(SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID =
tblListerListing.llStateID) AS BrokerID
or just do another inner join on tblBrokerTerritory.btTerritoryStateID =
tblListerListing.llStateID
Brian

If I take out the Dlookup part all is well but I really need that
Dlookup. Can someone explian why?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount,
tblListerListing.llStateID, tblListerListing.llTerritoryName FROM
tblLister INNER JOIN tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID"

I use the above because of the below:

If Not Me.cboListTypeID = "" Then
Select Case cboListTypeID
Case "A"
strFilterSQL = strSQL
Me.RecordSource = strFilterSQL
Case "M"
strFilterSQL = strSQL & " Where [BrokerID] =
Forms![frmLogOn]![cboLoginID];"
Me.Form.AllowAdditions = False
Me.RecordSource = strFilterSQL
Case Else
strFilterSQL = strSQL & " Where [llListingTypeID] = '" &
cboListTypeID & "';"
Me.RecordSource = strFilterSQL
End Select
End If

Thanks to anyone who can help,
Gina Whipp

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

Robert Morley

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


Rob

Brian said:
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 said:
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 said:
You should try to avoid those 'D' functions in your queries since they
are very slow.
Why don'y you try using a subquery instead...
(SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID =
tblListerListing.llStateID) AS BrokerID
or just do another inner join on tblBrokerTerritory.btTerritoryStateID =
tblListerListing.llStateID
Brian

If I take out the Dlookup part all is well but I really need that
Dlookup. Can someone explian why?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation, tblListerListing.llMonthlyRent,
tblListerListing.llMonthlyRevenue, tblListerListing.llAskingPrice,
tblListerListing.llGrossSDEPercent, tblListerListing.llGrossSDEAmount,
tblListerListing.llStateID, tblListerListing.llTerritoryName FROM
tblLister INNER JOIN tblListerListing ON tblLister.lListerID =
tblListerListing.llListerID"

I use the above because of the below:

If Not Me.cboListTypeID = "" Then
Select Case cboListTypeID
Case "A"
strFilterSQL = strSQL
Me.RecordSource = strFilterSQL
Case "M"
strFilterSQL = strSQL & " Where [BrokerID] =
Forms![frmLogOn]![cboLoginID];"
Me.Form.AllowAdditions = False
Me.RecordSource = strFilterSQL
Case Else
strFilterSQL = strSQL & " Where [llListingTypeID] = '" &
cboListTypeID & "';"
Me.RecordSource = strFilterSQL
End Select
End If

Thanks to anyone who can help,
Gina Whipp

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

Gina Whipp

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
B

Brian

ok.

Gina Whipp said:
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 said:
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 said:
Yes they are slow but trying to an inner join makes the query
UNupdateable. Ibelieve I may start over again and try another way...
--
Gina Whipp

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


You should try to avoid those 'D' functions in your queries since they
are very slow.
Why don'y you try using a subquery instead...
(SELECT btBrokerID FROM tblBrokerTerritory WHERE btTerritoryStateID =
tblListerListing.llStateID) AS BrokerID
or just do another inner join on tblBrokerTerritory.btTerritoryStateID
= tblListerListing.llStateID
Brian

If I take out the Dlookup part all is well but I really need that
Dlookup. Can someone explian why?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber,
DLookUp("btBrokerID","tblBrokerTerritory","[btTerritoryStateID]='" &
[llStateID] & "'") AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
tblListerListing.llInvestmentPotential,
tblListerListing.llListingTypeID,
tblListerListing.llTerritoryPopulation,
tblListerListing.llMonthlyRent, tblListerListing.llMonthlyRevenue,
tblListerListing.llAskingPrice, tblListerListing.llGrossSDEPercent,
tblListerListing.llGrossSDEAmount, tblListerListing.llStateID,
tblListerListing.llTerritoryName FROM tblLister INNER JOIN
tblListerListing ON tblLister.lListerID = tblListerListing.llListerID"

I use the above because of the below:

If Not Me.cboListTypeID = "" Then
Select Case cboListTypeID
Case "A"
strFilterSQL = strSQL
Me.RecordSource = strFilterSQL
Case "M"
strFilterSQL = strSQL & " Where [BrokerID] =
Forms![frmLogOn]![cboLoginID];"
Me.Form.AllowAdditions = False
Me.RecordSource = strFilterSQL
Case Else
strFilterSQL = strSQL & " Where [llListingTypeID] = '" &
cboListTypeID & "';"
Me.RecordSource = strFilterSQL
End Select
End If

Thanks to anyone who can help,
Gina Whipp

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