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?

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