Access error Type Num Div/0 Num

This tutorial explains the cause of Access errors in Access Query, Report, Form. These include #Error #Type! #Num! #Div/0! #Name.

Access error #Error #Type! #Num! #Div/0! #Name

There are several very common errors you may encounter in Access Query, Report or Form. It is very useful to understand the error in order to fix it, otherwise you will need to spend a lot of time to find out what Access is trying to tell you.

Access error #Error!

Access error #Error! occurs in an Expression where you try to do calculation on two Fields, however, Access cannot recognize one of them (or both of them) is Number so Access fails do a calculation.

For example, you cannot calculate Apple+1, because Apple is a Text.

Note that even if your Field is a Text, as long as the data is a number, Access can still do calculation.

Access error #Num!

There are many reasons that can cause Access #Num! error, below are two popular reasons.

Linked Table

In Linked Table, Access uses the first 8 rows of data to determine the data type of the Field. If Access sees the first 8 are Numbers, the data type is determined to be Number, but if you input Text in the 9th row, #Num! error occurs for that data.

Expression

If you divide 0 by 0, #Num! error occurs.

To solve the problem, use iff in Expression to capture this scnerio

iif(value1 = 0 AND value 2=0, 0, value1/value2)

Access error #Div/0!

If you divide a value by 0, #Div/0! occurs.

To solve the problem, use iff in Expression to capture this scenario

iif(value2 = 0,"your message", value1/value2)

For some calculations, this error is meaningful and should remain.

For example, if you are calculating an employee turnover rate

(number of leavers) / (headcount)

The headcount 0 has special implication and you should be alerted.

Access error #Type!

Access error #Type! occurs when two different Objects are used in a calculation, which means Access cannot recognize you are calculating two Numbers, Access thinks you are calculating a Table, a Query etc with a Number. This error is most likely caused by using inappropriate naming of the Fields, you should carefully note the Fields you use in calculation is not same as other Controls, such as Text Box name. If you can’t find any, perhaps you are using reserved name in Access.

For example, you should not name a Field as “Name”, because it is a reserved word referring to other Objects.

Click here to find out the list of reserved words.

You can either avoid using reserved words in Report/Form, or you can do your calculation in Query first.

Access error #Name

The cause of #Name Error is very similar to that of #Type! error. Below are three main reasons for #Name error.

Circular Reference

Field name is same as Control name (such as Text Box name). Access is confused whether to refer to the Field or Control, you should rename your Field or Control.

Reserved Word

Access has some reserved words that you should not use as field name.

Click here to find out the list of reserved words.

Data not updated

Access may not recognize the latest version of your data source, therefore Access cannot find the newly added Field name your are referring to in the source.

To refresh the data source, change data source to another table, and then change back to the data source.

Access error Type Num Div/0 Num