Overflow Message

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

When trying to sort or Group By a field with an expression, I get "Overflow"
message window. Just what is this telling me?
 
What is the expression? Often, that error results if you're trying to divide
by zero, or if a Null value is being used in a function that cannot handle a
Null value, or....

Instead of guessing, let's see the expression and that will help us suggest
possible solutions.
 
Thanks, I guess it would help, huh? I have a baseball statistics database
for which I'm trying to query a sort by the leading average hitter.
"Batting" is the table.
I've added an expression field to the query such as:

Batting![h]/Batting![ab]

The query runs with proper info, but once I try to sort the list by the
averages, or apply a Group By option to any of the fields, the Overflow
message pops up.

What am I missing?
 
As Ken stated, that error can occur if Batting![ab] is 0, or if either of
Batting![h] or Batting![ab] are Null.

Try:

IIf(Nz(Batting![ab], 0) = 0, 0, Nz(Batting![h],0)/Batting![ab])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


pskwaak said:
Thanks, I guess it would help, huh? I have a baseball statistics database
for which I'm trying to query a sort by the leading average hitter.
"Batting" is the table.
I've added an expression field to the query such as:

Batting![h]/Batting![ab]

The query runs with proper info, but once I try to sort the list by the
averages, or apply a Group By option to any of the fields, the Overflow
message pops up.

What am I missing?


Ken Snell (MVP) said:
What is the expression? Often, that error results if you're trying to
divide
by zero, or if a Null value is being used in a function that cannot
handle a
Null value, or....

Instead of guessing, let's see the expression and that will help us
suggest
possible solutions.
Click to expand...
Click to expand...
 
Hi Pskwaak,

You will also get a similar Overflow error if you attempt to apply a
criteria. Here is a solution that allows you to apply a criteria (average >=
0.3). It can also have the Group By option set, and be sorted by the
calculated batting average.

Create a new query. Dismiss the add table dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Copy the SQL (Structured Query Language) statement shown
below, and paste it into the SQL window, replacing the existing SELECT that
is highlighted. You can then return to normal query design view if you want,
by clicking on View > Design View.

SELECT Master.playerID, Batting.yearID, Batting.teamID,
Batting.AB, Batting.H, Pitching.W,
Format(IIf(Batting.AB=0,0,Batting.H/Batting.AB),"0.000") AS [AVE Batting]
FROM (Master INNER JOIN Batting ON Master.playerID = Batting.playerID)
INNER JOIN Pitching ON Master.playerID = Pitching.playerID
WHERE (((Batting.AB)<>0) AND ((Pitching.W)>20) AND
((Format(IIf([Batting].[AB]=0,0,[Batting].[H]/[Batting].[AB]),"0.000"))>=0.3))
ORDER BY Master.playerID, Batting.yearID;

Go Mariners! (Seattle Mariners)

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks, I guess it would help, huh? I have a baseball statistics database
for which I'm trying to query a sort by the leading average hitter.
"Batting" is the table.
I've added an expression field to the query such as:

Batting![h]/Batting![ab]

The query runs with proper info, but once I try to sort the list by the
averages, or apply a Group By option to any of the fields, the Overflow
message pops up.

What am I missing?
 
Thanks for the feedback - I'll work on that.

Douglas J. Steele said:
As Ken stated, that error can occur if Batting![ab] is 0, or if either of
Batting![h] or Batting![ab] are Null.

Try:

IIf(Nz(Batting![ab], 0) = 0, 0, Nz(Batting![h],0)/Batting![ab])


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


pskwaak said:
Thanks, I guess it would help, huh? I have a baseball statistics database
for which I'm trying to query a sort by the leading average hitter.
"Batting" is the table.
I've added an expression field to the query such as:

Batting![h]/Batting![ab]

The query runs with proper info, but once I try to sort the list by the
averages, or apply a Group By option to any of the fields, the Overflow
message pops up.

What am I missing?


Ken Snell (MVP) said:
What is the expression? Often, that error results if you're trying to
divide
by zero, or if a Null value is being used in a function that cannot
handle a
Null value, or....

Instead of guessing, let's see the expression and that will help us
suggest
possible solutions.

--

Ken Snell
<MS ACCESS MVP>

When trying to sort or Group By a field with an expression, I get
"Overflow"
message window. Just what is this telling me?
Click to expand...
Click to expand...
Click to expand...
 
Thanks for the feedback and info....I'll see how it works.

I will forgive you about the Mariners remark. :)

Giants forever
 
Thanks again, and, yes, I have downloaded and use that database.

Tom Wickerath said:
PS. For others who might be interested, the SQL statement that I gave works
in version 5.3 of the Lahman Baseball statistics database, which you can
download for free from here:

http://baseball1.com/statistics/


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Tom Wickerath said:
Hi Pskwaak,

You will also get a similar Overflow error if you attempt to apply a
criteria. Here is a solution that allows you to apply a criteria (average >=
0.3). It can also have the Group By option set, and be sorted by the
calculated batting average.

Create a new query. Dismiss the add table dialog without adding any tables.
In query design view, click on View > SQL View. You should see the word
SELECT highlighted. Copy the SQL (Structured Query Language) statement shown
below, and paste it into the SQL window, replacing the existing SELECT that
is highlighted. You can then return to normal query design view if you want,
by clicking on View > Design View.

SELECT Master.playerID, Batting.yearID, Batting.teamID,
Batting.AB, Batting.H, Pitching.W,
Format(IIf(Batting.AB=0,0,Batting.H/Batting.AB),"0.000") AS [AVE Batting]
FROM (Master INNER JOIN Batting ON Master.playerID = Batting.playerID)
INNER JOIN Pitching ON Master.playerID = Pitching.playerID
WHERE (((Batting.AB)<>0) AND ((Pitching.W)>20) AND
((Format(IIf([Batting].[AB]=0,0,[Batting].[H]/[Batting].[AB]),"0.000"))>=0.3))
ORDER BY Master.playerID, Batting.yearID;

Go Mariners! (Seattle Mariners)

Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Thanks, I guess it would help, huh? I have a baseball statistics database
for which I'm trying to query a sort by the leading average hitter.
"Batting" is the table.
I've added an expression field to the query such as:

Batting![h]/Batting![ab]

The query runs with proper info, but once I try to sort the list by the
averages, or apply a Group By option to any of the fields, the Overflow
message pops up.

What am I missing?
Click to expand...
Click to expand...
 
Back
Top