Access 2000 ADP - error when applying form filter - error message "The column prefix XXX does not ma

[Scott Crowley]

I have a form whose source data is a select query on three tables with inner
joins connecting them:



SELECT dbo.LOCATION.[Location code] AS LocationCode,
dbo.Clusters.ClusterNumber AS ClusterNumber,
dbo.Clusters.ClusterName AS ClusterName,
dbo.LOCATION.Cluster AS Cluster#,
dbo.ClusterStaffing.ITWorker AS PrimaryITWorker,
dbo.ClusterStaffing.WorkerStatus AS WorkerStatus,
dbo.ClusterStaffing.[Current] AS CurrentWorker,
dbo.LOCATION.Sitemapref, dbo.LOCATION.Address,
dbo.LOCATION.Phone
FROM dbo.LOCATION INNER JOIN
dbo.Clusters ON
dbo.LOCATION.Cluster = dbo.Clusters.ClusterNumber INNER JOIN
dbo.ClusterStaffing ON
dbo.Clusters.ClusterNumber = dbo.ClusterStaffing.ClusterNo
WHERE (dbo.ClusterStaffing.[Current] = 1) AND
(dbo.ClusterStaffing.WorkerStatus = 'Primary')
ORDER BY dbo.LOCATION.[Location code]



This is fine and the form loads and presents data as I expect. I can search
the form fine.



However, if I try to apply a form filter to one of the fields I get the
error message:



"The column prefix dbo.LOCATION does not match with a table name or alias
name used in the query"



and the filter is not applied.


Filter worked fine when the table source query only referenced one table.
Now I get this since adding more than one table.


I've searched for this error and seen it in relation to SQL queries than don
't run but not in this scenario.


Can anyone help?


Many thanks in advance.

Regards

Scott

 

[Sylvain Lafontaine]

First, maybe it is the caracter # in the Cluster# that gives DAP a hiccup.
Other things like « dbo.LOCATION.[Location code] » may also give you trouble
with DAP; even if they are perfectly valid under T-SQL. You may try to use
the profiler to see if there is anything unusual in the queries sent by DAP
to the SQL-Server in this situation.

Check also that in the data ouline window everything is OK. This window has
give me some surprises in the past. Sometime, removing the stored procedure
from the design, closing the design window, reopened it and then
re-inserting the stored procedure has been sufficient to make strange bugs
disappear.

Also, you don't give us what is the form filter that you are using and how
you are trying to apply it. Maybe the following article will be of some in
your case: http://support.microsoft.com/default.aspx?scid=kb;EN-US;275071

Finally, if anything else work, then you still have the option of replacing
your server filter with a parameterised SP.

S. L.

 

[Guest]

Try aliasing the table names:

SELECT L.[Location code] AS LocationCode,
C.ClusterNumber AS ClusterNumber,
C.ClusterName AS ClusterName,
L.Cluster AS Cluster#,
CS.ITWorker AS PrimaryITWorker,
CS.WorkerStatus AS WorkerStatus,
CS.[Current] AS CurrentWorker,
L.Sitemapref, L.Address,
L.Phone
FROM dbo.LOCATION L INNER JOIN
dbo.Clusters C ON
L.Cluster = C.ClusterNumber INNER JOIN
dbo.ClusterStaffing CS ON
C.ClusterNumber = CS.ClusterNo
WHERE (CS.[Current] = 1) AND
(CS.WorkerStatus = 'Primary')
ORDER BY L.[Location code]