Getting a division by zero error halting append query in macro

[nick243]

Was looking for any tips to avoid this issue right now that I am having. When I run this append query it gives me an "! cannot divide by 0". When I look at the query in design view I don't see any division problems. It has one instance of a multiplication problem [BE1j Final productivity]![dept Rate] * 1.2. I have looked to see if there is any zeros in the information tables but no luck. Any tips or help someone could provide would be appreciated.



Thanks,


Nick

[pbaldy]

Are those tables or queries that could have a division operation in them?

[nick243]

I will have to look at it tomorrow but one is a table (BI6B) and the other is a query (BE1j). I don't see any issue with bi6b. I may look more closely at be1j tomorrow.

[pbaldy]

The query would be my first suspect. Note typically you have to view the record to trigger the error in a select query.

[ssanfu]

A word of warning:

Table BI6B - "Level" and "Type" are reserved words and shouldn't be used as object names
Query BE1j - "Date" and "Year" are reserved words (and built in functions) and shouldn't be used as object names.

See Problem names and reserved words in Access



Some suggestions:
Use only letters and numbers (exception is the underscore) for object names.
Do not begin object names with a number.
Do not use spaces, punctuation or special characters in object names.
Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

Object names are field, table, query, form and/or report objects.

[nick243]

As far as reserved words, This query has worked for a long time. It has only seen a issue in the last day or two.

[JoeM]

As far as reserved words, This query has worked for a long time. It has only seen a issue in the last day or two.

Still, it is recommended practice to not use them.
Doing so can lead to confusion/ambiguity, unexpected results, and errors.

Regarding your errors, not saying that this is the issue here, but sometimes the error messages can be a little misleading.
Check for any errors in your data, or illegal operations (i.e. trying to use a text value in a mathematical operation).
Also, any Nulls in numerical fields can be problematic, especially if they are used in calculations.

Many times, it is a data issue. May sure you have strict data types to control the data.
A good test is to try it out on a real small sample of data (that is easy to verify all the values). If that works, then you know the issue is probably with the data itself, and not the structure of your query.

[CJ_London]

having spaces and non alpha numeric characters in field and table names or starting a field name with a number is poor practice and can also cause issues. As Joe says, not necessarily the issue here but can throw misleading error messages. As Access matures it has become less tolerant of poor naming conventions and you might have had an Access update which has tipped it over the edge.

In your error rate criteria you should be using a dot (.) rather than a bang (!)

Also since you are not aggregating any values, you should really be using SELECT DISTINCT, rather than an aggregate query.

Not sure why you have Associate ID Number in the query twice

Finally, perhaps the error is in the table you are appending to if it has a calculated field

[nick243]

Thank You everyone for the tips. I was able to find the error in the data and correct that by using criteria to filter out past employees that were getting #Error in the data. I was able to use criteria to eliminate past employees who were still showing in the query.

Thanks,
Nick