story so far.....

reports selects query and outputs to excel.....

Problem: output may be more than 66,000 rows.

Is there anyway I can get the output method to detect when say number of rows gets to 50,000 and then prompt the user to save a new excel file?


Any help appreciated.

jammy

Could you dcount() the query at some point?

Is there anyway I can get the output method to detect when say number of rows gets to 50,000 and then prompt the user to save a new excel file? No. You have to control the break yourself. Also, in order to make the code simple, you'll need to have a unique identifier in the recordset.

Use DCount() as ken suggested. Then if hte count is > 66000, run a query that orders the recordset by the unique identifier and selects the top 50000 rows. Output that query. Then subtract 50000 from the DCount() to find the remaining number of records. run a query that orders the recordset in descending order by the unique identifier and selects the top remainder rows. Output that query. If you could need to create more than 2 spreadsheets (and even if you don't think you ever will), you should really create a code loop that outputs 50000 rows at a time and then outputs the remainder.

Of course getting the "middle" sets is much more difficult. You will probably need to identify the primary key of the last record and use that in the criteria for subsequent queries.

Cheers Ken/Pat.....

I've just done just that but it appears that even exporting over 20,000 lines excel won't handle it.

My report creates about 70,000 lines.

Do you know if this is a settings problem?

Hum...

Maybe you have too many columns for a 20k row xls?

the report is only 2 columns wide.. this is why i'm scratching my head!!

Hum...

Maybe split it up as Pat says using date ranges or something to get smaller samples...

...sorry forgot to put: I get a message saying there are too many rows to output as specified by access!

You need to use the TransferSpreadsheet Method/Action. The OutputTo defaults to a very early version of Excel that only allows a small number of rows.