Solved

Overflow error when trying to output to Excel

Posted on 2001-06-05
9
573 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

730 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