Access 2000 ADP - error when applying form filter - error message "The column prefix XXX does not match with a table name or alias name used in the query"

From: Scott Crowley (scott_crowley_at_uk2.net)
Date: 11/18/04 

Date: Thu, 18 Nov 2004 15:02:34 -0000

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

Relevant Pages

  • Access 2000 ADP - error when applying form filter - error message "The column prefix XXX does n
    ... I have a form whose source data is a select query on three tables with inner ... dbo.Clusters.ClusterNumber AS ClusterNumber, ... FROM dbo.LOCATION INNER JOIN ...
    (microsoft.public.access.forms)
  • Re: Update query
    ... placing a between statement in the inner query and return the summed values. ... Same goes with the agg after the inner select statement. ... SET tblUploadCount = tblUploadCount + agg.TotalCnt, ... > UPDATE titles ...
    (microsoft.public.sqlserver.datamining)
  • Re: Dont know how to change SQL correctly.
    ... separate query first is not a problem. ... FROM tblProductLines RIGHT JOIN ((tblCompany INNER JOIN tblEmployees ON ... One solution is to first filter your inner table in a preliminary query, ...
    (microsoft.public.access.queries)
  • Re: Summing time worked by an individual on a weekly basis
    ... query going without the inapplicable records in the other tables change ... Inner joins say that a matching record has ... TypeOfTime (Toil, Lunch, Holiday, TravelTo, TravelFrom, Etc.) ... which pull out the data for each adviser from each table and then adds ...
    (microsoft.public.access.queries)
  • RE: Combining 3 queries
    ... I did not look over everything but in query 4 use left join instead of inner ... Prior Sales].[Prior Sales Value])))) ... INNER JOIN ([Inventory Prior Purchases] ...
    (microsoft.public.access.queries)