Solved Too many fields defined (1 Viewer)

[KitaYama]

I have this query

SELECT
      UnitPrice*Quantity AS Price,
      o.Delivery,
      o.ReID,
      p.DrawingNo,
      p.DrawingName
FROM
      tblOrders AS o INNER JOIN tblProducts AS p
      ON o.OrderProductFK = p.ProductPK
WHERE
      UnitPrice*Quantity>1000000
      AND o.Delivery>#10/30/2023#
      AND o.SetName Is Null
      AND o.OrderedFrom_FK=268
ORDER BY
      UnitPrice*Quantity DESC
;

This query shows the correct data.
If I select External Data -> Export To Excel I receive the following error.

too many fields defined.

If I delete p.DrawingName from query, export to excel works without any error.
If I substitute p.DrawingName with any other fields, again the same error shows up and export fails.

Both tables are linked table to sql server.
The query shows 252 records if double clicked and opened.

From Excel I can import the query without any problem.

Is there any limitation on the number of fields when exporting a query to Excel?
Thanks.

 

[June7]

The only limit is the Access limit of 255 fields in table or query.

 

[KitaYama]

tblOrders has 45 fields
tblProducts has 33 fields

Even if I sum them up, it's less than 255.

Thanks.

 

[MajP]

I would
Compact and repair
Close access
reboot your machine

If that does not work maybe try

Too Many Fields Defined error - Microsoft 365 Apps

Explains that you may receive an unexpected error message when you try to save a table after adding a new field or changing the properties of an existing field since the internal column count to track the number of fields in the table has reached 255.

learn.microsoft.com

​

 

[Pat Hartman]

I thought that once you added a table alias, you always had to alias columns from that table. Try changing all the references to UnitPrice and Quantity to o.UnitPrice and o.Quantity. I assume that's the table they're coming from.

 

[June7]

I tested. No table prefix is needed for any of the fields. Access will add them in Design View for fields not involved in calc but calc still works. I have no problem with export.

 

[ebs17]

DoCmd.RunSavedImportExport method (Access)

Office VBA reference topic

learn.microsoft.com

Do you use something like this? Then it could be that the field in question is not stored in the specification.

 

[June7]

@ebs17, OP stated they used the ribbon External Data Export dialog, not VBA.

 

[KitaYama]

I thought that once you added a table alias, you always had to alias columns from that table. Try changing all the references to UnitPrice and Quantity to o.UnitPrice and o.Quantity. I assume that's the table they're coming from.

As confirmed by @June7 the alias can be omitted. I tested again as you suggested just to be sure. No difference. The same error.

@ebs17 no, I don't. I 'm using ribbon's Export command (External Data).

Thanks.

 

[KitaYama]

I think I was able to solve the problem.

Ever since I installed the addin in Custom Ribbon Creator for Access 2019 and 2021 ,I had serious problems with Access.

All my addins were vanished, a lot of settings were changed. Even action queries didn't show the warning messages.
Some of these problems were confirmed by other members too. It was suggested to install the addin with running Access as Admin.

I uninstalled and reinstalled Office.
Now, everything's just OK. No error while exporting the query.

I really appreciate all for trying to help.
Million thanks.

 

[ebs17]

I 'm using ribbon's Export command

I'm a little surprised. If I otherwise do a lot of fine programming, I don't expose a user to basic Access functionality unaccompanied and unprotected somewhere else.

 

[arnelgp]

 

[KitaYama]

I'm a little surprised. If I otherwise do a lot of fine programming, I don't expose a user to basic Access functionality unaccompanied and unprotected somewhere else.

I'm sorry but I'm not sure if I can understand what you mean.
Do you mean why I'm using the ribbon and not programmatically exporting the data?

If it's so, it's a only-one-time action and not something that needs to be done on a specific routine.
One of our customers had asked for a report and some graphs for their orders (as a test). We wanted to offer something and if they accept the result, then a new feature will be added to FE. At this point we're not sure what their reply would be. For now we're only experimenting.
Hence at this stage, it was only a shot in the dark to see how their response would be.

If you meant something else, any further explanation would be great.
Thanks.

 

[spaLOGICng]

I have this query

SELECT
      UnitPrice*Quantity AS Price,
      o.Delivery,
      o.ReID,
      p.DrawingNo,
      p.DrawingName
FROM
      tblOrders AS o INNER JOIN tblProducts AS p
      ON o.OrderProductFK = p.ProductPK
WHERE
      UnitPrice*Quantity>1000000
      AND o.Delivery>#10/30/2023#
      AND o.SetName Is Null
      AND o.OrderedFrom_FK=268
ORDER BY
      UnitPrice*Quantity DESC
;

This query shows the correct data.
If I select External Data -> Export To Excel I receive the following error.

too many fields defined.

If I delete p.DrawingName from query, export to excel works without any error.
If I substitute p.DrawingName with any other fields, again the same error shows up and export fails.

Both tables are linked table to sql server.
The query shows 252 records if double clicked and opened.

From Excel I can import the query without any problem.

Is there any limitation on the number of fields when exporting a query to Excel?
Thanks.

You can export all fields via vba using DAO and the copyrecordset method.