Hi,

I looked through most of the history regarding these types of issues and i had no joy.

I'm directly inputting an SQL line into access 2000 into a database of about 50,000 records. When i do a search of fields either numeric or text and the records returned exceed something like10,000 i get the error "Overflow".

I can do a search of about 6,000 or so thousand and it returns records fine.

I have tried all the obvious things like using a double etc but one of the numeric fields is only a 4 digit number and i cant see it as being an issue but i ruled it out anyway.

Any help would be great thanks.

Pete

Pete,

Need more info here ...

"I'm directly inputting an SQL line into access 2000 into a database of about 50,000 records."

You're not putting the SQL statement into the database. Do you have a
query? Does it feed a form/report? What's the context of the error message?

Wayne

Im executing an SQL query that ive edited in SQL view, the query is below.

SELECT Last(idNum),
(First(price)-Last(price)) AS difference,
Min(dateCreated) AS minDate,
Max(dateCreated) AS maxdate,
Last(mobile) AS lastMobile,
Last(contents) AS lastContents,
(( (First(price)-Last(price)) /First(price) )*100) AS percentChange,
Last(price) AS lastPrice,
Last(state) AS lastState, Last(contact), Last(year)
FROM carAdds
GROUP BY idNum
HAVING (( Last([year]))>=2000)
ORDER BY (( (First(price)-Last(price)) /First(price) )*100) DESC;

I've extracted this from my code and i can get it to work if its only returning less than somthing under what i estimate to be 10,000 records. When i double click the query to execute it when ive specified an broad search criteria (ie anything less than 2003) it gives me the error "overflow".

I'm sure that the Where clause is more efficient than the Having clause, it might be worth a try

Im pretty sure in this case you cant use WHERE so you have to use HAVING

Isn't year a reserved word and thus a bad field name?

Changed it, still get the error.

Found the problem, i was dividing by zero, there should not have been blank/zero fields but for some reason there were so it was an oversight on my part.

Thanks for you help