#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    4
    Rep Power
    0

    Syntax Error in FROM Clause


    Hi,

    I'm working in quite a complex access 2000 database that has been created by a work collegue. The database contains tables, queries, forms, reports, macros and modules. I'm having trouble with a report (called COC) and unfortunately the creator is on holidays so I need some help fixing the problem.

    If I go to the design view of COC and edit anything (including a single label) and then go to the Print Preview I get the following message: "Syntax error in FROM Clause". The record source of the report comes from four tables and a query. The SQL for the report's record source is as follows:

    SELECT dt_PPK_CoC.chain_of_custody, PPK_COC_Sample.sample_date, PPK_COC_Sample.sample_time, PPK_COC_Sample.sample_name, PPK_COC_Sample.no_containers, PPK_COC_Sample.container_size, dt_sample.sample_matrix_code, dt_sample.custom_field_1, dt_sample.custom_field_2, PPK_COC_Sample.Hold, PPK_COC_Sample.pH, PPK_COC_Sample.Explosives, PPK_COC_Sample.Nitrocellulose, PPK_COC_Sample.TPH, PPK_COC_Sample.BTEX, PPK_COC_Sample.[Metals 8], PPK_COC_Sample.[Metals 16], PPK_COC_Sample.VOC, PPK_COC_Sample.SVOC, PPK_COC_Sample.PAH, PPK_COC_Sample.Phenols, PPK_COC_Sample.OPP, PPK_COC_Sample.OCP, PPK_COC_Sample.Herbicides, PPK_COC_Sample.Ammonia, PPK_COC_Sample.[Total Nitrogen], PPK_COC_Sample.TKN, PPK_COC_Sample.[Nitrate (as N)], PPK_COC_Sample.[Nitrite (as N)], PPK_COC_Sample.Cyanide, PPK_COC_Sample.[Dioxins and furans], PPK_COC_Sample.Asbestos, PPK_COC_Sample.PCBs, PPK_COC_Sample.[Synthetic Pyrethroids], PPK_COC_Sample.Barium, PPK_COC_Sample.Sulphate, PPK_COC_Sample.Other, PPK_COC_Sample.comment, dt_PPK_CoC.subcontractor_name_code, dt_PPK_CoC.delivery_method, dt_PPK_CoC.job_number, dt_PPK_CoC.project_manager, dt_PPK_CoC.turnaround, dt_PPK_CoC.results_expected, dt_PPK_CoC.results_format, dt_PPK_CoC.invoice_to, dt_PPK_CoC.comments, dt_PPK_CoC.samples_on_ice, rt_subcontractor.subcontractor_name, rt_subcontractor.subcontractor_contact_name, rt_subcontractor.address1, rt_subcontractor.city, rt_subcontractor.state, rt_subcontractor.zipcode, rt_subcontractor.phone_number, rt_subcontractor.fax_number
    FROM rt_subcontractor INNER JOIN ((dt_PPK_CoC INNER JOIN PPK_COC_Sample ON dt_PPK_CoC.chain_of_custody = PPK_COC_Sample.chain_of_custody) INNER JOIN dt_sample ON PPK_COC_Sample.dt_PPK_field_sample.sys_sample_code = dt_sample.sys_sample_code) ON rt_subcontractor.subcontractor_name_code = dt_PPK_CoC.subcontractor_name_code;

    Can anybody tell me if there's a problem in the SQL statement that would generate the message I get. Or is there another problem with the report that would lead to the error?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2003
    Posts
    38
    Rep Power
    12
    When I have a complex query like this I try to use the SQL plus to identify which part of the clause went bad.

    You'll have to adjust the syntax slightly to get it to run on the SQL command prompt but at least you'll know where it's bombing.

    Hope this helps.

    -V

IMN logo majestic logo threadwatch logo seochat tools logo