473,698 Members | 2,086 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Access SQL error "Overflow"

39 New Member
I keep getting the error: "Overflow" with this piece of MS Access SQL coding:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT 
  2.  
  3. (SELECT Count(tblCallLog.tblCallLog_gospelPresented) FROM tblCallLog WHERE tblCallLog.tblCallLog_callDate = Date() AND tblCallLog.tblCallLog_callDate Is Not Null AND tblCallLog.tblCallLog_gospelPresented = true) AS todaysGospelPres,
  4.  
  5. (SELECT Count(tblCallLog.tblCallLog_gospelPresented) FROM tblCallLog INNER JOIN tblClients ON tblCallLog.tblCallLog_clientID = tblClients.tblClients_clientID WHERE tblCallLog.tblCallLog_callDate = Date() AND tblCallLog.tblCallLog_callDate Is Not Null AND tblCallLog.tblCallLog_gospelPresented = true AND tblClients.tblClients_jewish = true) AS todaysGospelPresJewish,
  6.  
  7. (todaysGospelPres - todaysGospelPresJewish) AS todaysGospelPresGentile,
  8.  
  9. Format((todaysGospelPresJewish / todaysGospelPres), "Percent") AS todaysJewishGospelPercent,
  10.  
  11. (todaysGospelPresJewish & ' (' & todaysJewishGospelPercent & ')') AS comboTodayJewishGospel
  12.  
  13. FROM tblCallLog INNER JOIN tblClients ON tblCallLog.tblCallLog_clientID = tblClients.tblClients_clientID;
  14.  
Does anyone have an idea why?
Apr 2 '11 #1
1 8073
Stewart Ross
2,545 Recognized Expert Moderator Specialist
I think the most likely explanation is that there is a division by zero error happening, which gives rise to the overflow exception. Check the data in the underlying query carefully, and in particular look for the value of todaysGospelPre s being 0 somewhere. This field is an alias for the Count done in line 3.

If you check line 9 of the SQL statement above you will see that this count is used as the divisor in the expression

Format((todaysGospelPre sJewish / todaysGospelPre s), "Percent")

One way of avoiding such an error is to test the value, and if it is 0 substitute a suitable value instead. There is more than one way to do this, so here are two examples:

Expand|Select|Wrap|Line Numbers
  1. IIF(todaysGospelPres <> 0,Format((todaysGospelPresJewish / todaysGospelPres), "Percent"), null) 
  2.  
  3. Format((todaysGospelPresJewish / IIF(todaysGospelPres=0, 1, todaysGospelPres)), "Percent")
The first method has the advantage that a null value is substituted for the whole expression when the divisor is 0, leading to a blank result. The second example has the disadavantage that you will not necessarily know that there is a zero divisor in the underlying data for that row, as it does a safe 'division by 1' leaving the value being divided unchanged.

It would be worth separating out the Count subquery in line 3 for test purposes and running it to see if any of the rows returned are showing 0. Count does not normally return 0 values, but I guess the overflow is indicating some kind of fault in the underlying data.

The only other explanation I can think of is that the database engine is finding the use of the subqueries too complex. Again, this can be tested by separating the subqueries and testing that each runs on their own, checking what values are returned.

-Stewart
Apr 2 '11 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

1
2584
by: aj | last post by:
DB2 v8 FP5 Red Hat AS/EL 2.1 I found the following in my db2diag.log: 2004-09-07-10.01.37.747898 Instance:oltp Node:000 PID:14637(db2agent (NDRDB)) TID:8192 Appid:C0455584.K508.00E507141135 access plan manager sqlra_cache_mem_please Probe:21 Database:NDRDB Package Cache Number Overflows
2
17514
by: Andy Davis | last post by:
Dear Group I am trying to automate process of adding a new record id in my form using the following code when the user clicks the "Add New Record" button. For example if the last record id is "2005001" then I want to increment this by one so that new value is "2005002" and place it in the record id field for the new record on the form. I've tried using the following code but get the message "Overflow" when I run the code. Can't find on...
4
44107
by: Spook | last post by:
Running a particular Access report or the query associated with it brings up the warning "Overflow". What does this mean? WhatI have I done wrong? Both report and query were fine before lunch and the only thing I have changed is updated the data from one of the tables it pull info from. HELP!!!!!!!!!!!!!!!!!!! PLEASE
3
44732
by: SheldonMopes | last post by:
I sometimes get a pop-up box that reads "Overflow" and the module that is executing pauses. It doesn't get caught by my error trapping, and it seems to be randow. By random, I mean usually in the same section of code, but I can run the code a few times, then it appears. I can't reproduce it when I want to, and I have no idea what is causing it. Any ideas ? Thanks By the way the code is some record manipulation of 2 recordsets.
7
12095
by: Peter Ritchie | last post by:
I'm writing a Web Service and I would like to add performance counter data for monitoring performance of the Web Service's operations over time and load. The problem is, I get the "Requested registry access is not allowed." SecurityException when I try and create the performance counter category via PerformanceCounterCategory.Create(). I understand the login used to run the Web Service does not have access to the registry keys...
5
3305
xxoulmate
by: xxoulmate | last post by:
help on progress bar on error: "overflow" is the message., what is the cause of error., the progressbar value basis on the second(time) progressbar max value = 59 progressbar min value = 0
1
2985
by: rileyjane | last post by:
Hello, I am running a Union query in Access 2003 that combines two other queries. When I run each query individually, they open fine with no problems. But when I try to run the Union query, I get the Overflow message. I have seen in other posts that the Overflow message usually comes up when a query operation provides a result that is too large to fit in the underlying field size. But if that were the case, wouldn't I get the Overflow...
2
1864
by: johnwaddy | last post by:
I'm clueless to the problem, I have a couple of account with 300+ records and all of a sudden when you recalc it reaches record 256 and pops up this "Overflow" error. I do not have any issues with accounts with less than 256 records. Here is the VBA for the button I use on the form. Private Sub cmdCalc_Click() On Error GoTo Err_cmdCalc_Click Dim curDues As Currency Dim curFee As Currency Dim curCope As Currency Dim...
1
2715
by: Tami Robinson | last post by:
I keep getting !Overflow error when running this query: SELECT DISTINCTROW FamilyInfo.FamilyInfoID , Sum(IIf(IsNull(VitalPartnerCreditsQuery.VPCreditT),0,VitalPartnerCreditsQuery.VPCreditT)) AS TotVPCredit FROM VitalPartnerCreditsQuery RIGHT JOIN FamilyInfo ON VitalPartnerCreditsQuery.FamilyInfoID = FamilyInfo.FamilyInfoID GROUP BY FamilyInfo.FamilyInfoID; Any help would be appreciated. Thx
0
8604
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9157
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9028
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8895
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7728
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5860
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4369
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3046
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2330
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.