Too Many fields defined error (but doesn't seem to be)

  • Thread starter Thread starter Phill
  • Start date Start date
P

Phill

Firstly, good morning

I am trying to export data that i have in a query to an
Excel spreadsheet. I am familiar with this error usually
meaning that either a) a table has more than 255 fields,
or b) a query has more that 127 (as it uses a field name
twice) However my query does not, or at least i don't
think it does. I have included the SQL statement below.

The strange thing is though, i only get this error (too
many fields defined) when i am using VBA to export the
data:

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qry_DataExportToExcel", "S:\FaxDa
ta.xls", True

If i use a macro it works now and then, and when i open
the query it works fine everytime. The option of using a
macro is not available to me though as i have other lines
of VBA during looping so i cannot run the macro from code
(as i also need error handling on the procedure which you
cannot control on a macro)

Anyway, any advice would be gratefully appreciated.

Thanks in advance

Phill (MCP)

Query:
SELECT tbl_BulkFaxDMR.DMRNO, tbl_MainDMR.FaxNo,
tbl_MainDMR.SupName, tbl_MainDMR.PartNo,
tbl_MainDMR.PartName, tbl_MainDMR.DunsNo,
tbl_MainDMR.Problem, tbl_MainDMR.PRRNo,
tbl_MainDMR.DateEnt, tbl_Engineers.Engineer,
qry_TotalCostDowntime_MainandNonMLine.DTTotal AS
DownTimeTotal, qry_HoursTotalHoursCost.TotalHours,
qry_HoursTotalHoursCost.TotalHoursCost,
qry_TotalCostforExpense.Expensemat
FROM tbl_Engineers RIGHT JOIN ((((tbl_BulkFaxDMR INNER
JOIN tbl_MainDMR ON tbl_BulkFaxDMR.DMRNO =
tbl_MainDMR.DMRNO) INNER JOIN
qry_TotalCostDowntime_MainandNonMLine ON
tbl_BulkFaxDMR.DMRNO =
qry_TotalCostDowntime_MainandNonMLine.DMRNo) INNER JOIN
qry_HoursTotalHoursCost ON tbl_BulkFaxDMR.DMRNO =
qry_HoursTotalHoursCost.DMRNo) INNER JOIN
qry_TotalCostforExpense ON tbl_BulkFaxDMR.DMRNO =
qry_TotalCostforExpense.DMRNo) ON tbl_Engineers.EngId =
tbl_MainDMR.EngId;
 
Hi Phill,

I would try saving your query
results to a temp table and export the table
(or empty a correctly structured temp table,
append your query results to the temp table,
and export that temp table).

Just a hunch....but your query has several additional
queries and maybe those fields "count" in the process?

I don't know the internals of the process so
can only speculate (and not sure why "network"
complicates it). Again...just a hunch.

Good luck,

Gary Walter
 
Back
Top