473,729 Members | 2,234 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 8078
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
2586
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
17518
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
44112
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
44739
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
12099
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
3311
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
2994
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
1867
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
2716
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
8913
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9426
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
9280
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
9200
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,...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4525
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...
0
4795
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2677
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2162
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.