Overflow Error In Access Union Query
UNION query that has started to generate an "Overflow" error message when I execute it. The SQL is "select * from query1 union select * from query2". Both "query1" and "query2" execute fine when I try them separately - I only get the "Overflow" in the union query. Both "query1" and "query2" have identical fields (20 of them) in identical formats - the data is just sourced from two distinct tables. Only "query1" has a calculation to add two numbers together, so I can't see anything likely to cause a "divide by zero" error. What is particularly puzzling is that this union query has been used (unchanged) on a weekly basis for the last two years without error...! Anyone have any ideas? Thanks, Mark -- Mark Wickett - Adelaide Bank - Australia Mark Wickett, Jan 2, 2008 #1 Advertisements John Spencer Guest IF you are adding two integer numbers together and they exceed the maximum value of an integer field (32K) then you will get an overflow error. What are the field types involved? '==================================================== John Spencer Access MVP 2002-2005, 2007 Center for Health Program Development and Management University of Maryland Baltimore County '==================================================== Mark Wickett wrote: > Hi, > > I have a UNION query that has started to generate an "Overflow" error > message when I execute it. > > The SQL is "select * from query1 union select * from query2". > > Both "query1" and "query2" execute fine when I try them separately - I only > get the "Overflow" in the union query. Both "query1" and "query2" have > identical fields (20 of them) in identical formats - the data is just sourced > from two distinct tables. Only "query1" has a calculation to add two numbers > together, so I can't see anything likely to cause a "divide by zero" error. > > What is particularly puzzling is that this union query has been used > (unchanged) on a weekly basis for the last two years without error...! > > Anyone have any ideas? > > Thanks, > Mark > > -- > Mark Wickett - Adelaide Bank - Australia John Spencer, Jan 3, 2008 #2 Advertisements Mark Wickett Guest Hi John, Field types are both doubles - and actually, the nature of the source data is that one field is always negative (or zero), so the addition could only ever reduce the size of the result (it could possibly reduce it to a large negative though...!) I have looked at these fields (source and result) to confirm that these
Links Social Groups Pictures & Albums Members List Calendar Search Forums Show Threads Show Posts Tag Search Advanced Search Find All Thanked Posts Go to Page... Thread Tools Rate Thread Display Modes 04-11-2000, 01:28 AM #1 muratas2 Junior Member Join Date: Jan 2000 Location: istanbul / TURKEY Posts: 16 Thanks: 0 Thanked 0 Times in 0 Posts Overflow ploblem in Union Query Hi, I have a union query. I tried to union it whit another query output building a new union query. Although all the fields are matching, I'm receiving "OVERFLOW" error! This is the first time I'm facing this, where am I wrong? What can be the possible causes? Thanks in advance Murat muratas2 View Public Profile Find More Posts by muratas2 http://www.pcreview.co.uk/threads/overflow-message-in-union-query.3371640/ 04-12-2000, 12:25 AM #2 MattGilbert Junior Member Join Date: Mar 2000 Location: Brighton, UK Posts: 12 Thanks: 0 Thanked 0 Times in 0 Posts You might get this message if one of your fields is returning more than 254 characters - e.g. the data from a memo field. 254 seems to be a maximum. MattGilbert View Public Profile Visit MattGilbert's homepage! Find More Posts by MattGilbert « Previous Thread | Next Thread » Thread Tools Show http://www.access-programmers.co.uk/forums/showthread.php?t=25279 Printable Version Email this Page Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Rate This Thread Excellent Good Average Bad Terrible Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Access World Access World News Site Suggestions Introduce Yourself The Watercooler Microsoft Access Discussion General Tables Queries Forms Reports Macros Modules & VBA Theory and practice of database design Access Web Microsoft Access Reference Access FAQs Code Repository Sample Databases Microsoft Access Tutorials Microsoft Access User Groups Apps and Windows SQL Server Crystal Reports Visual Basic VB.NET Word Excel Web Design and Development ASP and ASP.NET PHP & MySQL Windows Other Software Hardware Questions and Answers Non-Access Issues Politics & Current Events Debates Gaming Sports, Health & Fitness Gadgets Small Business All times are GMT -8. The time now is 01:22 PM. Microsoft Access Help General Tables Queries Forms Reports Macros Modules & VBA Theory & Practice Access FAQs Code Repository Sample Databases Video Tutorials Sponsored Links How to advertise Media Kit Contact
query problem I have not come across before. I have a union query that incorporates 3 queries (each of which references tables and queries and contain a number of calculations). All 3 queries run fine. The union query was working fine, but now there is more data in http://www.office-forums.com/threads/overflow-error-on-union-query.687067/ the system, I get the following: If I do a normal Union, I get the "This expression is http://www.utteraccess.com/forum/Overflow-Error-SUM-t1918255.html typed incorrectly, or is too complex..." message. If I change it to Union All, the data appears in the data sheet for a second, then the message "Overflow" appears, then when the message is cleared, all the values in the datasheet are replaced by #Name?. I can scroll through the datasheet, but I get only 91 records when I should be getting 390. I have tried this in Access 97 and overflow error XP and get exactly the same result. There is a further query that takes the results of the Union query and filters/sums it. This query manages to return data, though I am not sure yet whether it is all the data or just a portion of it. I have tried simplifying the queries, but have gone about as far as I can down that route. I did read somewhere that overflow can be related to data types, but as I am doing a union and not doing any calculations overflow error in on the data in the 3underlying queries, that does not seem to be the problem. Can anyone shed any light on this? If not I may have to start making temp tables which will really bloat out the database if the users are not instructed to do a regular compact. Many thanks Paul Paul, Sep 23, 2003 #1 Advertisements Andy Wilkinson Guest I get a similar error with an attached XLS sheet which is used in a UNION query. If one of the date fields int the XLS is missing data I get the "too complex" error When I check the file and input the missing date all is OK. This may not be what is causing your error but it may just give you somewhere to look. Andy Perth Australia >-----Original Message----- >Hi > >I'm having a query problem I have not come across before. > >I have a union query that incorporates 3 queries (each of which references >tables and queries and contain a number of calculations). All 3 queries run >fine. > >The union query was working fine, but now there is more data in the system, >I get the following: > >If I do a normal Union, I get the "This expression is typed incorrectly, or >is too complex..." message. If I change it to Union All, the data appears in >the data sheet for a second, then the message "Overflow" appears, then when >the message is cleared, all the values in the datasheet are replaced by >#Name?. I can scroll through the datasheet, but I get only 91 record
Custom Search UtterAccess Forums>Microsoft® Access>Access Queries Overflow Error when using SUM() Forum HomeSearchHelpUA Messages|-- UtterAccess.com NewsAccess Knowledge Center|-- Access Code Archive|-- Access Knowledgebase / FAQ|-- Access TutorialsMicrosoft® Access|-- Local Access User Groups (AUGs)|-- Interface Design|-- Access Q and A|-- Access Tables + Relationships|-- Access Queries|-- Access Forms|-- Access Reports|-- Access Macros|-- Access Modules|-- Access Date + Time|-- Access Errors + Error Handling|-- Access Built-in Functions|-- Access Searching + Data Mining|-- Access Records|-- Access Security|-- Access Automation|-- Access Database Container|-- Tool bars, Menu bars + Ribbon|-- Access Services (Web Databases)|-- Access Data Access Pages (Deprecated)|-- Access Data Projects - ADP|-- Access Runtime, Packaging & DeploymentAnd More...|-- Microsoft SQL Server|-- SQL Server Reporting Services|-- Microsoft SharePoint|-- Microsoft Internet Explorer|-- Visual Basic 6 and Earlier|-- Visual Basic 2003 and Later|-- ASP and VBScript|-- Web Development|-- PHP, Perl, MySQL and Postgres|-- Other Database Engines|-- BI Tools and Applications|-- JAVA and C#|-- GraphicsMicrosoft® Office|-- Microsoft Excel|-- Microsoft Excel FAQs|-- Microsoft Word|-- Microsoft Outlook|-- Microsoft PowerPoint|-- Microsoft FrontPage/Exp. Web|-- Other Microsoft ProductsPC|-- Network Issues|-- Q & A - Hardware|-- Q & A - Software|-- Q & A - Other PC|-- Virus + Security DiscussionUtterAccess Odds and Ends|-- General Chat|-- Q & A - UtterAccess.com Forums|