Overflow error from query when criteria added

[blandrp]

I have a fairly simple table containing orders placed (410,412 rows) with the following pertinent columns:

Order#: Number (Double)
Date: Date/Time
#Items: Long Integer
OrderAmount: Currency


I have a query that is trying to return the orders from the past week with an average item price less than $175. This query has worked with no problems up until about 2 weeks ago when I started to get a popup containing only the word "Overflow" when I run the query.

I have set up a test query to narrow down the issue and have found the following. I can set up the following query and it will run with no issues:

Column 1 - Int([Date]) with criteria ">= (Date() - 7) And <= (Date() - 1)"
Column 2 - Order#
Column 3 - Date
Column 4 - Round([OrderAmount]/[#Items], 2)

As I said, running this query works fine. However, if I add the criteria "< 175" for column 4 above then I start to get the Overflow error!

Any ideas on why adding this criteria makes any difference? The smallest average item price is $27.88 and the largest is $2274.47 so I don't see how this could be a number format issue (which is what most of my google searches regarding the Overflow error have hinted at).

 

[PHV]

And with this criteria ?
<175.00

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[blandrp]

I tried using "<175.00" as well as "<175.01" and neither one works.

Just in case it might make a difference, this is happening in Access 2003 SP1. Also, I forgot to mention in my first post that the query without the "bad" criteria only brings back 2151 rows so I can't imagine that this might be caused by some issue with data volume.

 

[blandrp]

I've found another interesting twist to my issue. As I stated originally, putting criteria for column 1 without any criteria for column 4 works fine. The issue starts to occur when I have criteria for both columns 1 and 4. I tried to run the query with the criteria for column 4 but without any criteria for column 1, and the query runs fine. So, what is the problem with the combination of criteria for columns 1 and 4???

 

[dhookom]

I expect there are some records with either null or 0 [#Items].

Duane
Hook'D on Access
MS Access MVP

 

[blandrp]

Turns out there was a record that got a 0 for the #Items somehow - another issue to look into! Thanks for the help.