SUMMARY
When you open a table, a query, a form, or a report, you may see any of the
following error values:
#Error
#Num!
#Name?
#Div/0!
#Deleted
#Locked
Microsoft Access places one of these error values in a field when it cannot find necessary information, cannot carry out an expression, or cannot store a value within the limits set for the field.
The following sections explain what these error values mean, and list some
of the reasons why Access may display these error values.
#Error
The
#Error error value means that Access cannot evaluate an expression. For example, you may have supplied incorrect or too few arguments for an aggregate (totals) function, you may have used a parameter query as the domain for an aggregate function, or you may have made a circular reference in the expression.
The following three examples demonstrate possible causes for the
#Error value:
Aggregate function:
=DAvg("[Sales]","Employe")
In this example, the table name is misspelled. It should be "Employee" instead of "Employe."
Circular reference: (query)
FirstName: [FirstName] & " " & [LastName]
In this example, the alias FirstName is also part of the expression.
NOTE: In Microsoft Access 2000, this example generates the following error message
Circular reference caused by alias 'FirstName' in query definition's SELECT list.
Circular reference: (form or report)
Name: FirstName
ControlSource: =[FirstName] & " " & [LastName]
In this example, the text box name (FirstName) is also part of the expression.
In a table or form, this error can also occur when the
DefaultValue
property setting for a field or control is not appropriate for the
DataType or the
FieldSize property setting, or when an expression includes a bound control defined by using the
Trim() function, and the underlying field contains no data. The following example demonstrates a field whose
DefaultValue property setting is not appropriate for its
FieldSize property setting:
Field: State (Text, FieldSize = 2)
DefaultValue: ="Cal" (3 characters)
In a query, this error can occur when the value of a calculated field is
greater than the value allowed by the field's
FieldSize property setting. For example, if you add or multiply two integer values, and the resulting integer is greater than the value permitted in an integer field, Access displays the
#Error value in the field. The following example demonstrates a value that is larger than is permitted for the field:
Field: Age (Integer, current record value = 50)
Expr1: [Age] * 1000
#Num!
The
#Num! error value means that the value in the field is too large (either positively or negatively) to be stored in the field, based on the field's
DataType or
FieldSize property setting.
#Name?
The
#Name? error value means that the name that you typed as the source of the value in the field is not valid. You may have misspelled the name, you may have omitted the equal sign (=) before the expression, or the source itself may have been deleted.
The following example demonstrates a missing equal sign (=) in an
expression:
ControlSource: [FirstName] & " " & [LastName]
Control Source: =[FirstName] & " " & [LastName]
The following example demonstrates an invalid
ControlSource property name:
ControlSource: =[FirstNam] & " " & [LastName]
ControlSource: =[FirstName] & " " & [LastName]
In Microsoft Access 2000, an incorrect reference to a field in a subform or subreport returns
#Error rather than
#Name?.
Some other causes for the
#Name? error value include:
- A field name on the form or report that does not match the name of the field in the underlying table.
- A control name that is the same as one of the fields in the underlying table.
- An expression that is designed to calculate a sum for a control may
include a Sum() function. (You can use the Sum() function to calculate sums only for fields, not for controls.)
#Div/0!
The
#Div/0! error value means that you are trying to divide a number by zero (0), either directly in an expression (for example, 8/0), or by using a value from a field whose value is zero.
#Deleted
The
#Deleted error value means that the record being referred to has been deleted.
#Locked
The
#Locked error value can be caused by any of the following situations:
- The record has been locked by another user and Access cannot read the data.
- There are two or more instances of Access running on the
same computer. Access treats each open copy of the database as
a separate user.
- Your code has opened a recordset and has a lock on a record.
It is important to note that Access uses page locking instead
of record locking. Access stores records in groups of 2048
bytes, called pages. A single page may hold only one record, or it may hold
many records. If each record requires only 200 bytes, up to 10 records
may be stored on a page. When Access places a lock on a record,
it locks that record's page. If there are 10 records on the page, all
10 records on that page are locked.