Results 1 to 15 of 15
  1. #1
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52

    Exporting Report to Excel - Overflow error

    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!

  2. #2
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,933
    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

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,799
    you dont really export reports to Excel, you export data sheets (query)

  4. #4
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52
    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

  5. #5
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52
    Quote Originally Posted by Ajax View Post
    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!

  6. #6
    Welshgasman is offline Expert
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    622
    Quote Originally Posted by Anil Bagga View Post
    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?
    Please, please use # when posting code snippets.
    Cross Posting Etiquette: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?search_query=debug+access+vba



  7. #7
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,682
    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)
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  8. #8
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52
    Quote Originally Posted by Minty View Post
    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

  9. #9
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,263
    Quote Originally Posted by Anil Bagga View Post
    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.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,252
    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
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52
    Quote Originally Posted by Gicu View Post
    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
    Attached Files Attached Files

  12. #12
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    1,682
    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.
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓

  13. #13
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,252
    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
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  14. #14
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    52
    Quote Originally Posted by Gicu View Post
    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

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    2,252
    Great news Anil, just a hunch seeying those line feeds...


    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Exporting to Excel with formatting gives an error every other time
    By RasterImage in forum Import/Export Data
    Replies: 9
    Last Post: 11-19-2020, 04:05 PM
  2. Replies: 8
    Last Post: 02-15-2018, 12:10 AM
  3. Error exporting to Excel after 2016 upgrade
    By scotiwis in forum Access
    Replies: 2
    Last Post: 11-23-2017, 01:29 AM
  4. Replies: 4
    Last Post: 09-25-2012, 02:54 PM
  5. OVERFLOW error in both Query and Report
    By LanieB in forum Access
    Replies: 9
    Last Post: 11-11-2011, 08:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums