RE: Access 2000 ADP - error when applying form filter - error message

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Robert (Robert_at_discussions.microsoft.com)
Date: 11/26/04 

Date: Fri, 26 Nov 2004 06:07:09 -0800

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]

"Scott Crowley" wrote:

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