Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

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

Options
  • 10-06-2009 4:37pm
    #1
    Registered Users Posts: 7,541 ✭✭✭


    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?


Comments

  • Registered Users Posts: 7,265 ✭✭✭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
    


  • Closed Accounts Posts: 3,357 ✭✭✭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


  • Registered Users Posts: 7,541 ✭✭✭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.


  • Registered Users Posts: 7,265 ✭✭✭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?


  • Registered Users Posts: 7,541 ✭✭✭irlrobins


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


  • Advertisement
  • Registered Users Posts: 7,265 ✭✭✭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.]


  • Registered Users Posts: 7,541 ✭✭✭irlrobins


    Ok I'll give that a go. Thanks


  • Registered Users Posts: 7,541 ✭✭✭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.


  • Closed Accounts Posts: 1 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


Advertisement