Overflow

[CHaythorne]

Hi,

In my new job I've inherited an Access 2000 database that appears to have developed some issues while nobody was looking after it.

For the first 18 weeks of our FY this report was running correctly but then for some reason started throwing out 2 errors.

The first is when we run a macro, a query inside the macro is saying "overflow".

I've uploaded a screenshot of the Design View and SQL view of the query but have to admit, the query part is beyond my knowledge at present. It will be something I'll be looking to learn in the coming weeks.

Many Thanks for any help you can provide.

Cheers.

Chris

 

[JHB]

I think your create table query in the start, make a wrong/ to small field type!
Ex. If the field type is created as long and then you wants to store a decimal number.
A way out of this could be instead of creating a new table each time, you empty the table first and make select into the table. Because then you control which field type a field has.

 

[jdraw]

I agree with JHB. Instead of a MakeTable query ( table built each time query is run), create a table with the fields you need and the appropriate data types.

In your program logic,
-delete all existing records in the table(Delete query)
-, then run the query to Insert records into the table(append query)

Good luck.

 

[CHaythorne]

Thank you both for your help. I'll give this a shot later on.

Cheers.

 

[CHaythorne]

Hi,

Thank you for your help on this, I managed to solve the problem.

I followed your advice and re-made the query and created the table.

While I was doing this I noticed that some of the original data wasn't present in the table. This meant it was dividing numbers by 0.

Could this have also caused the overflow error?

Cheers.

 

[gemma-the-husky]

i was going to say that overflow often is the result of a divide by zero.

overflow occurs when the result of a calcluation is too big to fit in the variable. since div by zero is infinity, it is necessarily too big.

you always need to protect against both zero and null with this sort of thing.

iif(nz(x,0)=0,0,y/x)