Solved

Overflow error when trying to output to Excel

Posted on 2001-06-05
9
563 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
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
 
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now