Exporting Report to Excel - Overflow error

[Anil Bagga]

I have a report in Access which can be exported as PDF without a problem. When I try to export it to Excel I get an Overflow error. I have exported other reports multiple times and never faced an issue like this one

The explanation I got from Internet is as below

The "Overflow" error means that you are trying to put a number into a variable (or property etc), and the data type of the variable doesn't allow numbers that large.

The report converts to PDF without an error. Why is there no error in this case?

The report is based on a query which exports to Excel without an issue. I want to create a report for grouping etc but the overflow error is faced even before creating any groups etc!

[Ajax]

the explanation of overflow is correct

Pdf's do not have typed data so you would not get an overflow

as to why, really wouldn't have a clue without knowing what you are actually doing - but suspect you are trying to export with formatting

[ranman256]

you dont really export reports to Excel, you export data sheets (query)

[Anil Bagga]

The reason I want to export a report and not query is grouping and multilevel sorting. I have never had issues like this before in exporting reports

[Anil Bagga]

the explanation of overflow is correct

Pdf's do not have typed data so you would not get an overflow

as to why, really wouldn't have a clue without knowing what you are actually doing - but suspect you are trying to export with formatting

There is no option to export a report without formatting. This option is available when exporting a query. Exporting the query which is source data of report is NOT an issue!

[Welshgasman]

The reason I want to export a report and not query is grouping and multilevel sorting. I have never had issues like this before in exporting reports

Presumably never had such large values?

[Minty]

The most common cause of this error is a value exceeding around 32564 when it is declared as an Integer.
It could be a record count, a loop counter or even a customer ID or record ID that has just reached that value, and that id is being passed into a routine where it is declared as an Integer.

Something as innocuous as

Private Sub YourExportRoutine( CustomerID as Integer)

[Anil Bagga]

The most common cause of this error is a value exceeding around 32564 when it is declared as an Integer.
It could be a record count, a loop counter or even a customer ID or record ID that has just reached that value, and that id is being passed into a routine where it is declared as an Integer.

Something as innocuous as

Private Sub YourExportRoutine( CustomerID as Integer)

Thanks Minty. Will investigate. Unfortunately my DB is too big to be shared if I fail to find the root cause. The no of records for sure are not as many and customer ID's are text values

Let me check again

[ssanfu]

Unfortunately my DB is too big to be shared if I fail to find the root cause.

Just curious - does this mean that your dB is NOT split??

The no of records for sure are not as many

This always irks me... using "no" as an abbreviation for "Number" instead of using "Num". Having field names like "AddressNo" or "No_Of_Entrants" makes me wonder if there are fields like "AddressYes" or "Yes_Of_Entrants".


OK, I'm off my soapbox.....

[Gicu]

Hi Anil,

How many rows do you think the report would have (with all the extra ones for grouping)? More than 32767? See http://www.eileenslounge.com/viewtop...yle=12&p=95253

Also review this one:https://forums.codeguru.com/showthre...Overflow-Error

Cheers,
Vlad

[Anil Bagga]

Hi Anil,

How many rows do you think the report would have (with all the extra ones for grouping)? More than 32767? See http://www.eileenslounge.com/viewtop...yle=12&p=95253

Also review this one:https://forums.codeguru.com/showthre...Overflow-Error

Cheers,
Vlad

Hi Vlad

No. Number of rows in qry < 200. With groups etc would be < 230

I saw the links, There are no calculations with div by zero (see qry output in Excel and report output in PDF enclosed) and There are no cells > 1000 characters

I will try to trim the DB and upload as the last option

Regards
Anil

[Minty]

Showing us the output is not helping us. (I don't think, someone else may find differently)

We need to see the code you are using to export to Excel and the SQL and table data types of the source queries.

[Gicu]

Can you try to limit the query to FGCode Q0005 and see if you still get the error? I think column H FGSpecs is the one causing the error, looks like you have non-printing characters (new new line) in some of the records.

Cheers,
Vlad

[Anil Bagga]

Can you try to limit the query to FGCode Q0005 and see if you still get the error? I think column H FGSpecs is the one causing the error, looks like you have non-printing characters (new new line) in some of the records.

Cheers,
Vlad

Right on the money Vlad. I am amazed how you were able to diagnose this. Based on your post, when I looked at the 2 reports - PDF and XLS, the abbreviated text was an indicator but to get there.! How do you do it?

When copied the text on a text editor, the second line was there to see. Solution was simple and sure enough, RESOLVED. I had to go and edit the table, could not do it using the form for some reason. The root cause (it was prevalent in 5 other specifications) was they were copied from an Excel table!

Thanks you once again and it came at the right time as I was preparing the date to share in the forum

Regards
Anil

[Gicu]

Great news Anil, just a hunch seeying those line feeds...


Cheers,
Vlad