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
  #31  
Old February 27th, 2007, 04:35 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

No apologies needed... can take all the ideas I can get!!!

--
Gina Whipp

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


"SteveS" limbim53 at yahoo dot com wrote in message
...
My apologies for butting in........
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)



  #32  
Old February 27th, 2007, 05:46 PM 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

Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
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





















  #33  
Old February 27th, 2007, 06:05 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

Query still unupdateable

--
Gina Whipp

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


"Brian" s@y wrote in message ...
Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
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























  #34  
Old February 27th, 2007, 06:47 PM 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

Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID to
tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you wanted
it to update.

Brian

"Gina Whipp" wrote in message
...
Query still unupdateable

--
Gina Whipp

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


"Brian" s@y wrote in message
...
Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
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

























  #35  
Old February 27th, 2007, 09:37 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

Brian,

You missed one fo my responses ( 8-) I've typed so, I feel like I missed
one of my responses!!!) btTerritoryStateID is NOT a primary key or this
thing would have worked ages ago.

I got this whole thing to work by making the query Inconsistent Updates.
Not my first choice but after trying to convince the end-user to let me set
up the form. (In one of my former replies I stated that he insisted on
doing the forms.)

tblBrokerTerritory.btTerritoryStateID is the NOT primary key of
tblBrokerTerritory


"Brian" s@y wrote in message ...
Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID to
tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you
wanted it to update.

Brian

"Gina Whipp" wrote in message
...
Query still unupdateable

--
Gina Whipp

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


"Brian" s@y wrote in message
...
Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
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



























  #36  
Old February 27th, 2007, 10:54 PM 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

Oh, ok, well if you got it working that good.
Brian

"Gina Whipp" wrote in message
...
Brian,

You missed one fo my responses ( 8-) I've typed so, I feel like I missed
one of my responses!!!) btTerritoryStateID is NOT a primary key or this
thing would have worked ages ago.

I got this whole thing to work by making the query Inconsistent Updates.
Not my first choice but after trying to convince the end-user to let me
set up the form. (In one of my former replies I stated that he insisted
on doing the forms.)

tblBrokerTerritory.btTerritoryStateID is the NOT primary key of
tblBrokerTerritory


"Brian" s@y wrote in message
...
Gina,
Sorry I am probably misunderstanding your structures and relationships.
Is this what you have?
tblLister.lListerID is the primary key of tblLister
tblListerListing.llListingID is the primary key of tblListerListing
tblBrokerTerritory.btTerritoryStateID is the primary key of
tblBrokerTerritory
There is a 1-to-Many relation for tblLister.lListerID to
tblListerListing.llListerID, ie one Lister, multiple listinglistings.
There is a 1-to-Many relation for tblBrokerTerritory.btTerritoryStateID
to tblListerListing.llStateID, ie one tblBrokerTerritory, multiple
listinglistings.
If this is the case then the inner join is supposed to work and be
updateable while the subquery would not be updateable. I forgot you
wanted it to update.

Brian

"Gina Whipp" wrote in message
...
Query still unupdateable

--
Gina Whipp

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


"Brian" s@y wrote in message
...
Dina,
When you say they did not work, what exactly do you mean?

What is the structure of tblBrokerTerritory?
Brian

"Gina Whipp" wrote in message
...
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





























  #37  
Old February 28th, 2007, 05:13 AM posted to microsoft.public.access.formscoding,microsoft.public.access.queries,microsoft.public.access.modulescoding
SteveS
external usenet poster
 
Posts: 132
Default Why is this strSQL not working??? Please Help

OK,

In your original post you said: "If I take out the Dlookup part all is well
......".

What does the SQL string look like if you add: Debug.Print SQL ?



If you replace the DLOOKUP() with a value, say 5, is all still well?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber, 5 AS BrokerID, tblListerListing.llListingID,
tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
snip

Debug.Print SQL


What does the string look like?


I still say that embedding a VBA function inside a SQL string, the function
won't be evaluated. The function return value needs to be concatenated to the
SQL string.

Did you try concatenating the DLOOKUP() function to the SQL string (see my
first post)?

My ideas on your problem..... I'll back to sleep now.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


  #38  
Old February 28th, 2007, 05:29 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

SLEEP!!! You get to SLEEP???? I got it working Steve by using Inconsistent
Updates, not the best way but it works for what the end-user wants and no
talking him out of it (I tried).

--
Gina Whipp


"SteveS" limbim53 at yahoo dot com wrote in message
...
OK,

In your original post you said: "If I take out the Dlookup part all is
well
.....".

What does the SQL string look like if you add: Debug.Print SQL ?



If you replace the DLOOKUP() with a value, say 5, is all still well?

Const strSQL = "SELECT tblLister.lListerID, tblLister.lFirstName,
tblLister.lLastName, tblLister.leMailAddress, tblLister.lFaxNumber,
tblLister.lCellPhoneNumber, tblLister.lOtherPhoneNumber,
tblLister.lPhoneNumber, 5 AS BrokerID, tblListerListing.llListingID,

tblListerListing.llSaleAmount, tblListerListing.llInActiveOrSold,
snip

Debug.Print SQL


What does the string look like?


I still say that embedding a VBA function inside a SQL string, the
function
won't be evaluated. The function return value needs to be concatenated to
the
SQL string.

Did you try concatenating the DLOOKUP() function to the SQL string (see my
first post)?

My ideas on your problem..... I'll back to sleep now.

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)




 




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 11:58 AM.


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