• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

Overflow error when trying to output to Excel

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
Mister_Simon
Asked:
Mister_Simon
1 Solution
 
mcmahon_sCommented:
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
 
WonHopCommented:
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
 
Mister_SimonAuthor Commented:
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
Independent Software Vendors: 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!

 
WonHopCommented:
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
 
Mister_SimonAuthor Commented:
Thanks guys.. I used output to query instead of report
0
 
WonHopCommented:
Thanks Mister_Simon, I am glad I could help.

WonHop

0
 
tommyboydCommented:
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
 
WonHopCommented:
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
 
jabraham54Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now