MS Access - column prefix does not match with a table name or alias name used in the

irlrobins

Hey all,

This problem is wrecking my head so thought I'd look for some help.

MS Access frontend, connecting to SQL Server 2000.

I have a report that has it's data source set to:

SELECT     Requirement.HLTS_Ref, Requirement.Stream, Requirement.Req_No, Requirement.Req_Detail, Requirement.Document_No, 
                      Requirement.Pass_Fail, Requirement.Failure_Reason, Requirement.Finding_No, Document.Doc_No, Document.Doc_Name, Document.Version, 
                      Document.Date, Requirement.Notes, Requirement.Section_Heading, Requirement.ProjectNo, Project.ProjectName, Requirement.Sequence
FROM         Requirement INNER JOIN
                      Document ON Requirement.Document_No = Document.Doc_No INNER JOIN
                      Project ON Requirement.ProjectNo = Project.ProjectNo
WHERE     (Requirement.Scope <> 'No')
ORDER BY Requirement.Document_No, Requirement.Sequence

If I run this statement in SQL Server it runs fine and returns correct results.

This report (called report1) is called from a form using this code:

DoCmd.OpenReport "report1", acPreview, , "Doc_No = " & Forms!frmDocFilter.documentFilter.Value & " AND ProjectNo = " & GBL_ProjectNo

(the last parameter is the where parameter)

But when I do this I get "column prefix 'Requirement' does not match with a table name or alias name used in the query" error.

If I remove the ORDER BY in the data source of the report, it works fine. Anyone got any ideas?

RangeR

Run the PROPER SQL statement in SQL Server to see if it works. You are not testing like for like.

You have your main SQL Statement but when running your report, you are adding on extra where clauses. Add these clauses onto your original SQL Statement and see if it works. Obvioulsy give actual values in the SQL and not references to some unknown Access Object.

Also, in your first SQL, you are explicitly specifying table name prefixes to each field. You are NOT doing this in your report clause.

Try something like

SELECT     Requirement.HLTS_Ref, Requirement.Stream, Requirement.Req_No, Requirement.Req_Detail, Requirement.Document_No, 
                      Requirement.Pass_Fail, Requirement.Failure_Reason, Requirement.Finding_No, Document.Doc_No, Document.Doc_Name, Document.Version, 
                      Document.Date, Requirement.Notes, Requirement.Section_Heading, Requirement.ProjectNo, Project.ProjectName, Requirement.Sequence
FROM         Requirement INNER JOIN
                      Document ON Requirement.Document_No = Document.Doc_No INNER JOIN
                      Project ON Requirement.ProjectNo = Project.ProjectNo
WHERE     (Requirement.Scope <> 'No' [B][COLOR="Red"]AND Doc_No = 1 AND ProjectNo = 1[/COLOR][/B])
ORDER BY Requirement.Document_No, Requirement.Sequence

Maybe it should even be

SELECT     Requirement.HLTS_Ref, Requirement.Stream, Requirement.Req_No, Requirement.Req_Detail, Requirement.Document_No, 
                      Requirement.Pass_Fail, Requirement.Failure_Reason, Requirement.Finding_No, Document.Doc_No, Document.Doc_Name, Document.Version, 
                      Document.Date, Requirement.Notes, Requirement.Section_Heading, Requirement.ProjectNo, Project.ProjectName, Requirement.Sequence
FROM         Requirement INNER JOIN
                      Document ON Requirement.Document_No = Document.Doc_No INNER JOIN
                      Project ON Requirement.ProjectNo = Project.ProjectNo
WHERE     (Requirement.Scope <> 'No' [B][COLOR="Red"]AND Requirement.Doc_No = 1 AND Requirement.ProjectNo = 1[/COLOR][/B])
ORDER BY Requirement.Document_No, Requirement.Sequence

Beano

what is probably the problem is the

AND ProjectNo = " & GBL_ProjectNo

bit. In the main query there is a ProjectNo field in two tables. When you call it from access it is having trouble figuring out which ProjectNo field you are referring to. Try changing it to

AND Requirement.ProjectNo = " & GBL_ProjectNo

irlrobins

RangeR wrote: »

Maybe it should even be

SELECT     Requirement.HLTS_Ref, Requirement.Stream, Requirement.Req_No, Requirement.Req_Detail, Requirement.Document_No, 
                      Requirement.Pass_Fail, Requirement.Failure_Reason, Requirement.Finding_No, Document.Doc_No, Document.Doc_Name, Document.Version, 
                      Document.Date, Requirement.Notes, Requirement.Section_Heading, Requirement.ProjectNo, Project.ProjectName, Requirement.Sequence
FROM         Requirement INNER JOIN
                      Document ON Requirement.Document_No = Document.Doc_No INNER JOIN
                      Project ON Requirement.ProjectNo = Project.ProjectNo
WHERE     (Requirement.Scope <> 'No' [B][COLOR="Red"]AND Requirement.Doc_No = 1 AND Requirement.ProjectNo = 1[/COLOR][/B])
ORDER BY Requirement.Document_No, Requirement.Sequence

This works fine when run in SQL Server. But leaving the query as

SELECT dbo.Requirement.HLTS_Ref, dbo.Requirement.SFP_Stream, dbo.Requirement.Req_No, dbo.Requirement.Req_Detail, 
                      dbo.Requirement.Document_No, dbo.Requirement.Pass_Fail, dbo.Requirement.Failure_Reason, dbo.Requirement.Finding_No, dbo.Document.Doc_No, 
                      dbo.Document.Doc_Name, dbo.Document.Version, dbo.Document.Date, dbo.Requirement.Notes, dbo.Requirement.Section_Heading, 
                      dbo.Requirement.ProjectNo, dbo.Project.ProjectName, dbo.Requirement.Sequence
FROM         dbo.Requirement INNER JOIN
                      dbo.Document ON dbo.Requirement.Document_No = dbo.Document.Doc_No INNER JOIN
                      dbo.Project ON dbo.Requirement.ProjectNo = dbo.Project.ProjectNo
WHERE     (dbo.Requirement.Scope <> 'No')

and in the form using

"Requirement.Document_No = " & Forms!frmLLTSDocFilter.documentFilter.Value & " AND Requirement.projectno  = " & GBL_ProjectNo

I get the same error message.

RangeR

You have

"Requirement.Document_No = " & Forms!frmLLTSDocFilter.documentFilter.Value & " AND Requirement.projectno  = " & GBL_ProjectNo

Where it should be

"Requirement.[COLOR="Red"][B]Doc_No[/B][/COLOR] = " & Forms!frmLLTSDocFilter.documentFilter.Value & " AND Requirement.projectno  = " & GBL_ProjectNo

Just a typo on this post or typo in Access Code?
Same error?
Are they numeric fields or text/varchar?

irlrobins

The field is called Document_No in the Requirement table and Doc_No in the Document table.

RangeR

Are you using linked tables and Access queries or are you using SQL Server Pass Through Queries.

You should be using pass through for performance.

Fook, Do this....

• Convert it to a stored procedure with two parameters [can you guess which ones?]
• On your form, just call the report with no where clause parameter
• On the report, Set the recordsource to be a passthrough query.
• In the Report OnLoad method [or on the form, just before running report], run some code to alter a pass through query SQL property to be the calling of the SP with parameter.

It's been a while since I've done Access programming but I have no doubt this will work [considering you have it working in SQL anyway.]

irlrobins

Ok I'll give that a go. Thanks

irlrobins

Ok got it working the way I wanted finally.

I created a stored procedure as ranger suggested taking into two parameters.

I then pass the parameters using this code in the Open event of the report:

Dim strRecordSource As String
strRecordSource = "Exec [sp_docLLTS] '" & GBL_ProjectNo & "', '" & Forms!frmLLTSDocFilter.documentFilter.Value & "'"
Me.RecordSource = strRecordSource

I then call the report from the form using DoCmd.OpenReport without a where clause.

Thanks for the help guys.

shijobaby

the column prefix does not match with a table name or alias name used in

the query.

I was also working to solve this error

Nw i found why it is comming just needs to make small changes in query


http://sqlerrormessages.blogspot.com/2009/08/sql-server-error-messages-msg-107.html