Access SQL error "Overflow"

[Amy Badgett]

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

SELECT DISTINCT 
 
(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,
 
(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,
 
(todaysGospelPres - todaysGospelPresJewish) AS todaysGospelPresGentile,
 
Format((todaysGospelPresJewish / todaysGospelPres), "Percent") AS todaysJewishGospelPercent,
 
(todaysGospelPresJewish & ' (' & todaysJewishGospelPercent & ')') AS comboTodayJewishGospel
 
FROM tblCallLog INNER JOIN tblClients ON tblCallLog.tblCallLog_clientID = tblClients.tblClients_clientID;
 

Does anyone have an idea why?

[Stewart Ross]

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 todaysGospelPres 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((todaysGospelPresJewish / todaysGospelPres), "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:

IIF(todaysGospelPres <> 0,Format((todaysGospelPresJewish / todaysGospelPres), "Percent"), null) 
 
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