Freak Error on SQL FROM Statement

  • Thread starter Thread starter MonTpython
  • Start date Start date
M

MonTpython

I am having a problem while setting up a select query that uses the results
of a form's multiple select list box as a filter. Everything works just fine
but I am getting a strange error. Whenever I go to open the query and it
tells me:

"The specified field 'StoreID' could refer to more than one table listed in
the FROM clause of your SQL statement"

The current FROM statement is as follows:
FROM ((tblMasterStoreTable LEFT JOIN tblBlueGreen ON
tblMasterStoreTable.StoreID = tblBlueGreen.StoreID) LEFT JOIN tlnkInitiatives
ON tblMasterStoreTable.StoreID = tlnkInitiatives.Store) LEFT JOIN
tblBandwidth ON tblMasterStoreTable.StoreID = tblBandwidth.Store

What is odd is that if I open the query itself in design view and click on
anything and then try to open the query, it no longer sees an error and opens
just fine (if I open it but don't click on anything it still gives the same
error). I haven't changed anything (I took snapshots of the SQL statement
before and after and their identical) but somehow it needs me to click on
something within design view before it will let me see the query results.

Any ideas on what might be triggering the error.
Thanks in advance,
MonT
 
I think you'll find that StoreID appears in the SELECT portion of the query,
and the message is trying to tell you that StoreID needs to be qualified
with a table name.
 
Here is the SELECT portion as it stands:

SELECT tblMasterStoreTable.RegionID, tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID, tblMasterStoreTable.VetFlag,
tblMasterStoreTable.HotelFlag, tblBlueGreen.StoreType, tlnkInitiatives.PI,
tblBandwidth.BandUp

I'm not sure but I believe that everything is there. If this was the error
(since nothing changes after I click in the query) it wouldn't stop reporting
the error and let me run the query would it?

I did check again to be absolutely sure what triggers letting me run the
query and it is me going in design mode and selecting StoreID "Table" drop
down and re-selecting the same one that it is already showing selected.
Again, nothing changes in the SQL statement after I do this, beyond the error
going away.

MonT
 
Every time that the StoreID comes up in the SQL code it is always explicitly
defined with the table name.

MonT
 
So are there any other ideas as to what might be causing the error to come
up. It is so frustrating since nothing actually changes in the SQL, but if I
want the query to run I have to open it in design view and reselect the
source table from the drop down (not changing anything just reassuring Access
that this is REALLY what I want) and then it runs the query without a hitch.
Thanks,
MonT
 
Here is the complete SQL code from the Query.
I can't see where the error would be coming from:

SELECT tblMasterStoreTable.RegionID, tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID, tblMasterStoreTable.[Vet Flag],
tblMasterStoreTable.[Hotel Flag], tblBlueGreen.StoreType, tlnkInitiatives.PI,
tblBandwidth.BandUp

FROM ((tblMasterStoreTable LEFT JOIN tblBlueGreen ON
tblMasterStoreTable.StoreID = tblBlueGreen.StoreID) LEFT JOIN tlnkInitiatives
ON tblMasterStoreTable.StoreID = tlnkInitiatives.Store) LEFT JOIN
tblBandwidth ON tblMasterStoreTable.StoreID = tblBandwidth.Store

WHERE (((tblMasterStoreTable.DistrictID) In ("Alabama","Baltimore","C Nj","C
Ohio","C Ontario")));

If you have any ideas, please let me know, I can't get anywhere with this
thing.
Doug - The last bit is from the multi select list box which populates the
District names to the filter that you helped develop in VBA last month.
Thanks again,
MonT
 
If you're using a multi-select list box, then presumably you're building the
SQL in VBA code.

Are you then storing the SQL in a QueryDef object?

Let's see all of your VBA code.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Here is the complete SQL code from the Query.
I can't see where the error would be coming from:

SELECT tblMasterStoreTable.RegionID, tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID, tblMasterStoreTable.[Vet Flag],
tblMasterStoreTable.[Hotel Flag], tblBlueGreen.StoreType,
tlnkInitiatives.PI,
tblBandwidth.BandUp

FROM ((tblMasterStoreTable LEFT JOIN tblBlueGreen ON
tblMasterStoreTable.StoreID = tblBlueGreen.StoreID) LEFT JOIN
tlnkInitiatives
ON tblMasterStoreTable.StoreID = tlnkInitiatives.Store) LEFT JOIN
tblBandwidth ON tblMasterStoreTable.StoreID = tblBandwidth.Store

WHERE (((tblMasterStoreTable.DistrictID) In ("Alabama","Baltimore","C
Nj","C
Ohio","C Ontario")));

If you have any ideas, please let me know, I can't get anywhere with this
thing.
Doug - The last bit is from the multi select list box which populates the
District names to the filter that you helped develop in VBA last month.
Thanks again,
MonT

Douglas J. Steele said:
I think you'll find that StoreID appears in the SELECT portion of the
query,
and the message is trying to tell you that StoreID needs to be qualified
with a table name.
Click to expand...
Click to expand...
 
Does the WHERE clause ever have StoreID in it, unqualified by table name?
That will also cause that error.

MonTpython said:
Here is the complete SQL code from the Query.
I can't see where the error would be coming from:

SELECT tblMasterStoreTable.RegionID, tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID, tblMasterStoreTable.[Vet Flag],
tblMasterStoreTable.[Hotel Flag], tblBlueGreen.StoreType,
tlnkInitiatives.PI,
tblBandwidth.BandUp

FROM ((tblMasterStoreTable LEFT JOIN tblBlueGreen ON
tblMasterStoreTable.StoreID = tblBlueGreen.StoreID) LEFT JOIN
tlnkInitiatives
ON tblMasterStoreTable.StoreID = tlnkInitiatives.Store) LEFT JOIN
tblBandwidth ON tblMasterStoreTable.StoreID = tblBandwidth.Store

WHERE (((tblMasterStoreTable.DistrictID) In ("Alabama","Baltimore","C
Nj","C
Ohio","C Ontario")));

If you have any ideas, please let me know, I can't get anywhere with this
thing.
Doug - The last bit is from the multi select list box which populates the
District names to the filter that you helped develop in VBA last month.
Thanks again,
MonT

Douglas J. Steele said:
I think you'll find that StoreID appears in the SELECT portion of the
query,
and the message is trying to tell you that StoreID needs to be qualified
with a table name.
Click to expand...
Click to expand...
 
Back
Top