Overflow Error

[Don]

I am using Office 2007. I attempted to run a report that had always run with
no problem, in the past. This time, I received an error message - - Overflow.

I tried to run the underlying query and got the same error message. I have
no idea what caused this error or how to fix it. Any help would be
appreciated.

 

[Jerry Whittle]

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

It's possible that you are trying something with a datatype that can't
handle it. For example trying to turn today's date into a Integer. Something
like:

Debug.Print CInt(Now())

See if there are any functions called in the query or the size your data is
getting near goverment spending.

 

[Don]

Here is the SQL view of this queries code:

SELECT DISTINCT Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT,
tblCUSTOMER_LIST.SALES_RT, tblDSR.DSR, tblINV_TOTAL.CUST_NO,
tblCUSTOMER_LIST.CUSTOMER, tblINV_TOTAL.ORDER_TYPE, tblINV_TOTAL.INVOICE_NO,
tblINV_TOTAL.DEL_DATE, tblINV_TOTAL.DAY, tblINV_TOTAL.TOT_PRICE,
tblINV_TOTAL.TOT_LOT_CST, tblINV_TOTAL.TOT_LOT_PFT, [TOT_LOT_PFT]/[TOT_PRICE]
AS LOT_MAR, tblTOTAL_INV_LINES.count, Right([tblINV_TOTAL].[INVOICE_NO],2) AS
[CHECK], qryFUEL.ITEM_NO
FROM qryFUEL RIGHT JOIN (tblTOTAL_INV_LINES INNER JOIN ((tblINV_TOTAL INNER
JOIN tblCUSTOMER_LIST ON tblINV_TOTAL.CUST_NO = tblCUSTOMER_LIST.CUST_NO)
INNER JOIN tblDSR ON tblCUSTOMER_LIST.SALES_RT = tblDSR.SALES_RT) ON
(tblTOTAL_INV_LINES.CUST_NO = tblINV_TOTAL.CUST_NO) AND
(tblTOTAL_INV_LINES.INVOICE_NO = tblINV_TOTAL.INVOICE_NO)) ON
qryFUEL.INVOICE_NO = tblINV_TOTAL.INVOICE_NO
ORDER BY Abs([tblCUSTOMER_LIST].[SALES_RT]), tblCUSTOMER_LIST.CUSTOMER;

 

[Jerry Whittle]

Hummm. Nothing too strange there.

I'd remove the following lines one at a time to see what happens:

[TOT_LOT_PFT]/[TOT_PRICE] AS LOT_MAR,

Right([tblINV_TOTAL].[INVOICE_NO], 2) AS [CHECK],

Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT plus the Order By clause.

I noticed that the WHERE includes qryFUEL RIGHT. Assuming that I's a query,
the problem could be in it also. You might want to post its SQL statement and
check for functions in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL view of this queries code:

SELECT DISTINCT Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT,
tblCUSTOMER_LIST.SALES_RT, tblDSR.DSR, tblINV_TOTAL.CUST_NO,
tblCUSTOMER_LIST.CUSTOMER, tblINV_TOTAL.ORDER_TYPE, tblINV_TOTAL.INVOICE_NO,
tblINV_TOTAL.DEL_DATE, tblINV_TOTAL.DAY, tblINV_TOTAL.TOT_PRICE,
tblINV_TOTAL.TOT_LOT_CST, tblINV_TOTAL.TOT_LOT_PFT, [TOT_LOT_PFT]/[TOT_PRICE]
AS LOT_MAR, tblTOTAL_INV_LINES.count, Right([tblINV_TOTAL].[INVOICE_NO],2) AS
[CHECK], qryFUEL.ITEM_NO
FROM qryFUEL RIGHT JOIN (tblTOTAL_INV_LINES INNER JOIN ((tblINV_TOTAL INNER
JOIN tblCUSTOMER_LIST ON tblINV_TOTAL.CUST_NO = tblCUSTOMER_LIST.CUST_NO)
INNER JOIN tblDSR ON tblCUSTOMER_LIST.SALES_RT = tblDSR.SALES_RT) ON
(tblTOTAL_INV_LINES.CUST_NO = tblINV_TOTAL.CUST_NO) AND
(tblTOTAL_INV_LINES.INVOICE_NO = tblINV_TOTAL.INVOICE_NO)) ON
qryFUEL.INVOICE_NO = tblINV_TOTAL.INVOICE_NO
ORDER BY Abs([tblCUSTOMER_LIST].[SALES_RT]), tblCUSTOMER_LIST.CUSTOMER;

--
Don Rountree

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

It's possible that you are trying something with a datatype that can't
handle it. For example trying to turn today's date into a Integer. Something
like:

Debug.Print CInt(Now())

See if there are any functions called in the query or the size your data is
getting near goverment spending.

 

[Don]

I've discovered what is causing the problem, although I don't understand why.
A simple equation in my query is causing the problem: LOT_MAR:
[TOT_LOT_PFT]/[TOT_PRICE]. It is a valid equation . . . can someone tell me
why it doesn't work?
--
Don Rountree

Here is the SQL view of this queries code:

SELECT DISTINCT Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT,
tblCUSTOMER_LIST.SALES_RT, tblDSR.DSR, tblINV_TOTAL.CUST_NO,
tblCUSTOMER_LIST.CUSTOMER, tblINV_TOTAL.ORDER_TYPE, tblINV_TOTAL.INVOICE_NO,
tblINV_TOTAL.DEL_DATE, tblINV_TOTAL.DAY, tblINV_TOTAL.TOT_PRICE,
tblINV_TOTAL.TOT_LOT_CST, tblINV_TOTAL.TOT_LOT_PFT, [TOT_LOT_PFT]/[TOT_PRICE]
AS LOT_MAR, tblTOTAL_INV_LINES.count, Right([tblINV_TOTAL].[INVOICE_NO],2) AS
[CHECK], qryFUEL.ITEM_NO
FROM qryFUEL RIGHT JOIN (tblTOTAL_INV_LINES INNER JOIN ((tblINV_TOTAL INNER
JOIN tblCUSTOMER_LIST ON tblINV_TOTAL.CUST_NO = tblCUSTOMER_LIST.CUST_NO)
INNER JOIN tblDSR ON tblCUSTOMER_LIST.SALES_RT = tblDSR.SALES_RT) ON
(tblTOTAL_INV_LINES.CUST_NO = tblINV_TOTAL.CUST_NO) AND
(tblTOTAL_INV_LINES.INVOICE_NO = tblINV_TOTAL.INVOICE_NO)) ON
qryFUEL.INVOICE_NO = tblINV_TOTAL.INVOICE_NO
ORDER BY Abs([tblCUSTOMER_LIST].[SALES_RT]), tblCUSTOMER_LIST.CUSTOMER;

--
Don Rountree

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

It's possible that you are trying something with a datatype that can't
handle it. For example trying to turn today's date into a Integer. Something
like:

Debug.Print CInt(Now())

See if there are any functions called in the query or the size your data is
getting near goverment spending.

 

[Don]

Thank you for your suggestions. I went back and looked at my data and
discovered a zero that was gumming up the first calculation that you
mentioned. I added a condition to the formula to account for dividing by
zero, and problem solved.

Thanks for your help.
--
Don Rountree

Hummm. Nothing too strange there.

I'd remove the following lines one at a time to see what happens:

[TOT_LOT_PFT]/[TOT_PRICE] AS LOT_MAR,

Right([tblINV_TOTAL].[INVOICE_NO], 2) AS [CHECK],

Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT plus the Order By clause.

I noticed that the WHERE includes qryFUEL RIGHT. Assuming that I's a query,
the problem could be in it also. You might want to post its SQL statement and
check for functions in it.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Here is the SQL view of this queries code:

SELECT DISTINCT Abs([tblCUSTOMER_LIST].[SALES_RT]) AS RT,
tblCUSTOMER_LIST.SALES_RT, tblDSR.DSR, tblINV_TOTAL.CUST_NO,
tblCUSTOMER_LIST.CUSTOMER, tblINV_TOTAL.ORDER_TYPE, tblINV_TOTAL.INVOICE_NO,
tblINV_TOTAL.DEL_DATE, tblINV_TOTAL.DAY, tblINV_TOTAL.TOT_PRICE,
tblINV_TOTAL.TOT_LOT_CST, tblINV_TOTAL.TOT_LOT_PFT, [TOT_LOT_PFT]/[TOT_PRICE]
AS LOT_MAR, tblTOTAL_INV_LINES.count, Right([tblINV_TOTAL].[INVOICE_NO],2) AS
[CHECK], qryFUEL.ITEM_NO
FROM qryFUEL RIGHT JOIN (tblTOTAL_INV_LINES INNER JOIN ((tblINV_TOTAL INNER
JOIN tblCUSTOMER_LIST ON tblINV_TOTAL.CUST_NO = tblCUSTOMER_LIST.CUST_NO)
INNER JOIN tblDSR ON tblCUSTOMER_LIST.SALES_RT = tblDSR.SALES_RT) ON
(tblTOTAL_INV_LINES.CUST_NO = tblINV_TOTAL.CUST_NO) AND
(tblTOTAL_INV_LINES.INVOICE_NO = tblINV_TOTAL.INVOICE_NO)) ON
qryFUEL.INVOICE_NO = tblINV_TOTAL.INVOICE_NO
ORDER BY Abs([tblCUSTOMER_LIST].[SALES_RT]), tblCUSTOMER_LIST.CUSTOMER;

--
Don Rountree

Show us the SQL of the query. Open the query in design view. Next go to
View, SQL View and copy and past it here.

It's possible that you are trying something with a datatype that can't
handle it. For example trying to turn today's date into a Integer. Something
like:

Debug.Print CInt(Now())

See if there are any functions called in the query or the size your data is
getting near goverment spending.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I am using Office 2007. I attempted to run a report that had always run with
no problem, in the past. This time, I received an error message - - Overflow.

I tried to run the underlying query and got the same error message. I have
no idea what caused this error or how to fix it. Any help would be
appreciated.