Solved

Overflow error when trying to output to Excel

Posted on 2001-06-05
9
579 Views
Last Modified: 2008-02-01
Hi all,

I have the statement:

DoCmd.OutputTo acOutputReport, "rpt_CallArrival", acFormatXLS, strOutFile

Which when run gives a "6 - Run-time overflow error"

Any ideas ?
0
Comment
Question by:Mister_Simon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 4

Expert Comment

by:mcmahon_s
ID: 6156095
Is there a total on the report of some very large numbers (depending on the base data types it may not need to be that big). You may have a total that has exceeded the maximum number for its data type.
0
 
LVL 2

Expert Comment

by:WonHop
ID: 6156115
Hello Mister Simon.  I had the same problem.  This is the answer that I received from here.  It help me understand what was going on.

Here is an article from MS http://support.microsoft.com/support/kb/articles/Q201/5/89.ASP?LN=EN-US&SD=tech&FR=0, it says it applies to Access 2000 but it sounds like the problem is with
Excel.

Hope this helps.

WonHop
0
 

Author Comment

by:Mister_Simon
ID: 6156357
mcmahon_s,

All number datatypes are long's and the highest number on the report is 1532 which is easy small enough.

WonHop,

When I try to analyse it with Excel it indeeds give me the error "There are two many rows to output, based on the limitation specified by the output format or by Microsoft Access. Why is this ?? As the report is only 1 page long and the table from the query has approximately 23 rows.

Any more ideas ?

Thanks.

Simon
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 2

Accepted Solution

by:
WonHop earned 50 total points
ID: 6156442
Simon:  
What I ended up doing was, putting all of the information I needed in a Temp Table and then running everything from there.  I created a Delete query to empty the Temp table, an Append query to load it again and I ran my Report from that table.  

I am not sure if this is correct or not, but it seemed like Access was reading the total number of records in the Main Table instead of what was in the Query.  But when I ran everything from the Temp table, everything started working just fine.

Other than that,  I am not really sure.

WonHop
0
 

Author Comment

by:Mister_Simon
ID: 6159091
Thanks guys.. I used output to query instead of report
0
 
LVL 2

Expert Comment

by:WonHop
ID: 6160010
Thanks Mister_Simon, I am glad I could help.

WonHop

0
 

Expert Comment

by:tommyboyd
ID: 7982633
i think im looking for a query simlar to the one you stated

"I created a Delete query to empty the Temp table"

could you give me the code please?
0
 
LVL 2

Expert Comment

by:WonHop
ID: 7990101
I use this code to run my querys.


DoCmd.SetWarnings False

    stDocName = "qdel_tbl_Count_LT_Children_Temp"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

DoCmd.SetWarnings True


WonHop
0
 

Expert Comment

by:jabraham54
ID: 8354945
I know you found and accepted answer but here is some additional feedback
Anyway, to make a long story short, I found the problem (at least with my situation).  It turns out that I had fields in my table that looked like null or spaces.  What was there, was unprintable characters that caused the overflow.
I wrote an update query that had a function that converted the ‘unprintables’ to null.

Function ContainsValidAsc_TSB(strIn As Variant) As Variant
  ' Comments  : Determines if there are valid alphanumeric characters; if any char is invalid then set to null
  ' Parameters: strIn - string to check
  ' Returns   : True if the string contains alpha characters other than "-", False otherwise
  '
  Dim intCounter As Integer
  Dim chrTmp As String * 1
  Dim fAlpha As Boolean

  fAlpha = True
  If IsNull(strIn) Then
    fAlpha = False
  Else
      For intCounter = 1 To Len(strIn)
        chrTmp = Mid$(strIn, intCounter, 1)
        If Asc(chrTmp) < 32 Or Asc(chrTmp) > 122 Or Asc(chrTmp) = 94 Or Asc(chrTmp) = 96 Then
            fAlpha = False
            Exit For
        End If
      Next intCounter
End If

    If fAlpha Then
        ContainsValidAsc_TSB = Trim(strIn)
    Else
        ContainsValidAsc_TSB = Null
    End If

End Function

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question