Math and Syntax Query Problems!

[shuff]

I have two problems in one query. I've been working on this one for a couple of days now, so I'm ready to say UNCLE!

I am writing an update query where the update is conditional on a calculated field - if Unused Credit ([AT03]) is at least 80% of Total Credit ([AT01]), then update field XYZ with a value of 10.

FIRST PROBLEM: If the denominator [AT01] is zero, the calculation fails because Divide By Zero is non sequitur. The query result for that record is #ERROR, but it displays in the query results, nevertheless.

SECOND (More important) PROBLEM: To get the "at least 80%" part, I need to set criteria for that calculated field to be >.8, but when I add the criteria, I just get a message that says "Overflow." I've written alot of stuff in Access, but I've never seen "Overflow" pop up as the result of a query.

I am looking for a way to explain and eliminate the "Overflow" condition, which is possibly caused by the Divide By Zero issue. One idea I had was to get around having to divide by changing the calculation to be [AT03] * 1/[AT01], but I don't know how to do a 1/x construct in Access.

Thank you in advance for any ideas.

SLH

 

[KKilfoil]

Elimination of Div0:

Create a new query that includes all of the fields from your table(s) that are now the source for your query. Add <>0 as a criteria for [AT01].
Have your problem query use this new query as source.


I suspect your overflow is due to divide by zero.


BTW, As a personal rule of thumb, I ALWAYS ensure that I have my data exactly as I want it (aggregation, calculations, etc) using select query(s), and then have any action queries (such as update queries) use the final select query as source. I find it much easier to use select queries when debugging, because I can preview without changing any data.

 

[shuff]

What a perfect example of not seeing the forest for the trees! I'll try this this afternoon but I'll bet it works just fine.

Many thanks,
SLH