Overflow error on query

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

Guest

When I try run the following query I get an overflow error -

SELECT DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength, Sum(SlitSales.[#Cheeses]) AS
[SumOf#Cheeses]
FROM (LocRef RIGHT JOIN ((SlitSales INNER JOIN DateConvPt2 ON SlitSales.ID =
DateConvPt2.ID) INNER JOIN RevItem ON SlitSales.ID = RevItem.ID) ON
LocRef.Entity = SlitSales.Entity) INNER JOIN RollLengthCalc ON SlitSales.ID =
RollLengthCalc.ID
GROUP BY DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength;

I have tried to narrow the problem down and found that the
RollLengthCalc.NormLength field is the problem. I can remove or add as many
other fields as I like but as soon as this field is included I get the
overflow error and as soon as I remove this field the query runs fine.

Below is the SQL that creates this suspect field -

SELECT SlitSales.ID,
([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![Width]/1000) AS
AvgLength, IIf([AvgLength]<700,"<700",IIf([AvgLength]>=700 And
[AvgLength]<=1250,"1000",IIf([AvgLength]>=1251 And
[AvgLength]<=1750,"1500",IIf([AvgLength]>=1751 And
[AvgLength]<=2249,"2000",IIf([AvgLength]>=2250 And
[AvgLength]<=2750,"2500",IIf([AvgLength]>=2751 And
[AvgLength]<=3300,"3000","other")))))) AS NormLength
FROM SlitSales;

What have I screwed up?
 
Don't put all them quotes around your numbers in that unusually long IIf()
function as well as the last "Other" which can't be sum'd. I would actually
create a simple user-defined function that returns the numeric value based
on M2, #Cheeses, and Width.

I don't ever use a calculated value/column as part of an expression in
another column/expression. Also, the field Width may cause issues since
Width is a property of many objects in Access.
 
Thanks for the quick reply.

I don't plan on using these numbers as numerical values, I am just trying to
bucket each sales record into a Length category. If there is a better way of
doing this than using the nested IIf's I would be very keen to know.

Could you also point me in the right direction for creating a "simple
user-defined function". This sound great but I would not know where to start.

Thanks in advance.


Duane Hookom said:
Don't put all them quotes around your numbers in that unusually long IIf()
function as well as the last "Other" which can't be sum'd. I would actually
create a simple user-defined function that returns the numeric value based
on M2, #Cheeses, and Width.

I don't ever use a calculated value/column as part of an expression in
another column/expression. Also, the field Width may cause issues since
Width is a property of many objects in Access.

--
Duane Hookom
MS Access MVP


SthOzNewbie said:
When I try run the following query I get an overflow error -

SELECT DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength, Sum(SlitSales.[#Cheeses]) AS
[SumOf#Cheeses]
FROM (LocRef RIGHT JOIN ((SlitSales INNER JOIN DateConvPt2 ON SlitSales.ID
=
DateConvPt2.ID) INNER JOIN RevItem ON SlitSales.ID = RevItem.ID) ON
LocRef.Entity = SlitSales.Entity) INNER JOIN RollLengthCalc ON
SlitSales.ID =
RollLengthCalc.ID
GROUP BY DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength;

I have tried to narrow the problem down and found that the
RollLengthCalc.NormLength field is the problem. I can remove or add as
many
other fields as I like but as soon as this field is included I get the
overflow error and as soon as I remove this field the query runs fine.

Below is the SQL that creates this suspect field -

SELECT SlitSales.ID,
([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![Width]/1000) AS
AvgLength, IIf([AvgLength]<700,"<700",IIf([AvgLength]>=700 And
[AvgLength]<=1250,"1000",IIf([AvgLength]>=1251 And
[AvgLength]<=1750,"1500",IIf([AvgLength]>=1751 And
[AvgLength]<=2249,"2000",IIf([AvgLength]>=2250 And
[AvgLength]<=2750,"2500",IIf([AvgLength]>=2751 And
[AvgLength]<=3300,"3000","other")))))) AS NormLength
FROM SlitSales;

What have I screwed up?
Click to expand...
Click to expand...
 
Took my own advice and got rid of the ugly nested IIf and came up with the
below which gives me the result I'm after by rounding to the nearest multiple
of 500 -

SELECT [SlitSales].[ID],
Int(((([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![ChWidth]/1000)/1000)*2)+0.5)*0.5*1000 AS NormLength
FROM SlitSales;

However, as soon as I 'GROUP BY' this field in another query I get the
overflow error.

There is still something wrong with something.


SthOzNewbie said:
Thanks for the quick reply.

I don't plan on using these numbers as numerical values, I am just trying to
bucket each sales record into a Length category. If there is a better way of
doing this than using the nested IIf's I would be very keen to know.

Could you also point me in the right direction for creating a "simple
user-defined function". This sound great but I would not know where to start.

Thanks in advance.


Duane Hookom said:
Don't put all them quotes around your numbers in that unusually long IIf()
function as well as the last "Other" which can't be sum'd. I would actually
create a simple user-defined function that returns the numeric value based
on M2, #Cheeses, and Width.

I don't ever use a calculated value/column as part of an expression in
another column/expression. Also, the field Width may cause issues since
Width is a property of many objects in Access.

--
Duane Hookom
MS Access MVP


SthOzNewbie said:
When I try run the following query I get an overflow error -

SELECT DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength, Sum(SlitSales.[#Cheeses]) AS
[SumOf#Cheeses]
FROM (LocRef RIGHT JOIN ((SlitSales INNER JOIN DateConvPt2 ON SlitSales.ID
=
DateConvPt2.ID) INNER JOIN RevItem ON SlitSales.ID = RevItem.ID) ON
LocRef.Entity = SlitSales.Entity) INNER JOIN RollLengthCalc ON
SlitSales.ID =
RollLengthCalc.ID
GROUP BY DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength;

I have tried to narrow the problem down and found that the
RollLengthCalc.NormLength field is the problem. I can remove or add as
many
other fields as I like but as soon as this field is included I get the
overflow error and as soon as I remove this field the query runs fine.

Below is the SQL that creates this suspect field -

SELECT SlitSales.ID,
([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![Width]/1000) AS
AvgLength, IIf([AvgLength]<700,"<700",IIf([AvgLength]>=700 And
[AvgLength]<=1250,"1000",IIf([AvgLength]>=1251 And
[AvgLength]<=1750,"1500",IIf([AvgLength]>=1751 And
[AvgLength]<=2249,"2000",IIf([AvgLength]>=2250 And
[AvgLength]<=2750,"2500",IIf([AvgLength]>=2751 And
[AvgLength]<=3300,"3000","other")))))) AS NormLength
FROM SlitSales;

What have I screwed up?
Click to expand...
Click to expand...
Click to expand...
 
I would check for either 0 or null values in any of the fields.

--
Duane Hookom
MS Access MVP


SthOzNewbie said:
Took my own advice and got rid of the ugly nested IIf and came up with the
below which gives me the result I'm after by rounding to the nearest
multiple
of 500 -

SELECT [SlitSales].[ID],
Int(((([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![ChWidth]/1000)/1000)*2)+0.5)*0.5*1000
AS NormLength
FROM SlitSales;

However, as soon as I 'GROUP BY' this field in another query I get the
overflow error.

There is still something wrong with something.


SthOzNewbie said:
Thanks for the quick reply.

I don't plan on using these numbers as numerical values, I am just trying
to
bucket each sales record into a Length category. If there is a better way
of
doing this than using the nested IIf's I would be very keen to know.

Could you also point me in the right direction for creating a "simple
user-defined function". This sound great but I would not know where to
start.

Thanks in advance.


Duane Hookom said:
Don't put all them quotes around your numbers in that unusually long
IIf()
function as well as the last "Other" which can't be sum'd. I would
actually
create a simple user-defined function that returns the numeric value
based
on M2, #Cheeses, and Width.

I don't ever use a calculated value/column as part of an expression in
another column/expression. Also, the field Width may cause issues since
Width is a property of many objects in Access.

--
Duane Hookom
MS Access MVP


When I try run the following query I get an overflow error -

SELECT DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength, Sum(SlitSales.[#Cheeses])
AS
[SumOf#Cheeses]
FROM (LocRef RIGHT JOIN ((SlitSales INNER JOIN DateConvPt2 ON
SlitSales.ID
=
DateConvPt2.ID) INNER JOIN RevItem ON SlitSales.ID = RevItem.ID) ON
LocRef.Entity = SlitSales.Entity) INNER JOIN RollLengthCalc ON
SlitSales.ID =
RollLengthCalc.ID
GROUP BY DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength;

I have tried to narrow the problem down and found that the
RollLengthCalc.NormLength field is the problem. I can remove or add
as
many
other fields as I like but as soon as this field is included I get
the
overflow error and as soon as I remove this field the query runs
fine.

Below is the SQL that creates this suspect field -

SELECT SlitSales.ID,
([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![Width]/1000)
AS
AvgLength, IIf([AvgLength]<700,"<700",IIf([AvgLength]>=700 And
[AvgLength]<=1250,"1000",IIf([AvgLength]>=1251 And
[AvgLength]<=1750,"1500",IIf([AvgLength]>=1751 And
[AvgLength]<=2249,"2000",IIf([AvgLength]>=2250 And
[AvgLength]<=2750,"2500",IIf([AvgLength]>=2751 And
[AvgLength]<=3300,"3000","other")))))) AS NormLength
FROM SlitSales;

What have I screwed up?
Click to expand...
Click to expand...
Click to expand...
 
Yes, Div/0 was the problem. "Overflow" threw me off the scent.

Thanks.

Duane Hookom said:
I would check for either 0 or null values in any of the fields.

--
Duane Hookom
MS Access MVP


SthOzNewbie said:
Took my own advice and got rid of the ugly nested IIf and came up with the
below which gives me the result I'm after by rounding to the nearest
multiple
of 500 -

SELECT [SlitSales].[ID],
Int(((([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![ChWidth]/1000)/1000)*2)+0.5)*0.5*1000
AS NormLength
FROM SlitSales;

However, as soon as I 'GROUP BY' this field in another query I get the
overflow error.

There is still something wrong with something.


SthOzNewbie said:
Thanks for the quick reply.

I don't plan on using these numbers as numerical values, I am just trying
to
bucket each sales record into a Length category. If there is a better way
of
doing this than using the nested IIf's I would be very keen to know.

Could you also point me in the right direction for creating a "simple
user-defined function". This sound great but I would not know where to
start.

Thanks in advance.


:

Don't put all them quotes around your numbers in that unusually long
IIf()
function as well as the last "Other" which can't be sum'd. I would
actually
create a simple user-defined function that returns the numeric value
based
on M2, #Cheeses, and Width.

I don't ever use a calculated value/column as part of an expression in
another column/expression. Also, the field Width may cause issues since
Width is a property of many objects in Access.

--
Duane Hookom
MS Access MVP


When I try run the following query I get an overflow error -

SELECT DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength, Sum(SlitSales.[#Cheeses])
AS
[SumOf#Cheeses]
FROM (LocRef RIGHT JOIN ((SlitSales INNER JOIN DateConvPt2 ON
SlitSales.ID
=
DateConvPt2.ID) INNER JOIN RevItem ON SlitSales.ID = RevItem.ID) ON
LocRef.Entity = SlitSales.Entity) INNER JOIN RollLengthCalc ON
SlitSales.ID =
RollLengthCalc.ID
GROUP BY DateConvPt2.MonthYear, LocRef.LocCode, RevItem.RevItem,
SlitSales.Width, RollLengthCalc.NormLength;

I have tried to narrow the problem down and found that the
RollLengthCalc.NormLength field is the problem. I can remove or add
as
many
other fields as I like but as soon as this field is included I get
the
overflow error and as soon as I remove this field the query runs
fine.

Below is the SQL that creates this suspect field -

SELECT SlitSales.ID,
([SlitSales]![M2]/[SlitSales]![#Cheeses])/([SlitSales]![Width]/1000)
AS
AvgLength, IIf([AvgLength]<700,"<700",IIf([AvgLength]>=700 And
[AvgLength]<=1250,"1000",IIf([AvgLength]>=1251 And
[AvgLength]<=1750,"1500",IIf([AvgLength]>=1751 And
[AvgLength]<=2249,"2000",IIf([AvgLength]>=2250 And
[AvgLength]<=2750,"2500",IIf([AvgLength]>=2751 And
[AvgLength]<=3300,"3000","other")))))) AS NormLength
FROM SlitSales;

What have I screwed up?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cannot determine casue of overflow error 1
Query works on dev db, gets a Run-time error '3073': Operation mustuse an updateable query on Pre-pr 1

Back
Top