ACC: Troubleshooting Tips for Error Values

• Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

You see any of the following error values in a field on a table, a query, a form, or a report:

   #Error, #Num!, #Name?, #Div/0!, #Deleted, or #Locked

CAUSE

Microsoft Access places an error value in a field when it cannot find necessary information, carry out an expression, or store a value within the limits for the field.

RESOLUTION

The sections below explain what the error values mean, and list some reasons why Microsoft Access may display them.

#Error

This value means that Microsoft Access cannot evaluate the expression. For example, you may have supplied incorrect or too few arguments for an aggregate (totals) function, used a parameter query as the domain for an aggregate function, or made a circular reference in the expression.

The following three examples demonstrate possible causes for the #Error value:

   Aggregate function:
      =DAvg("[Sales]","Employe")

   Circular reference: (Query)
      FirstName: [FirstName] & " " & [LastName]

NOTE: In Microsoft Access 7.0 and 97 the above example generates the error message "Circular reference caused by alias 'FirstName' in query definition's SELECT list."

   Circular reference: (Forms and Reports)
      Name: FirstName
      ControlSource: =[FirstName] & " " & [LastName]

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 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 setting:

   Field: State (Text, FieldSize = 2)
   DefaultValue: ="Cal" (3 characters)

   Field: Age (Integer, current record value = 50)
   Expr1: [Age] * 1000

#Num!

This 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?

This error means that the name entered as the source of the value in the field is invalid. The name may be misspelled, 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]
   Should be: =[FirstName] & " " & [LastName]

   ControlSource: =[FirstNam] & " " & [LastName]
   Should be =[FirstName] & " " & [LastName]

   =Forms![<MainFormName>]![<SubFormName>].Form![<ControlName>]

   -or-

   =Reports![<MainReportName>]![<SubReportName>].Report![<ControlName>]

<SubFormName> or <SubReportName> is the name of the subform or subreport Note that this name does not have to be the same name as the name of the subform or subreport itself. To verify this name, check the Name property of the subform or subreport.

<ControlName> is the name of the control on the subform or subreport. To verify this name, check the Name property of the control.

NOTE: In Microsoft Access 7.0 and 97 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 on the underlying table.
• An expression that is designed to calculate a sum for a control may include a Sum() function. (The Sum() function can be used to calculate sums only for fields, not for controls.)

#Div/0!

This value means that you are trying to divide a number by zero, either directly in an expression (for example, 8/0), or by using a value from a field whose value is zero.

#Deleted

This value means that the record being referred to has been deleted.

#Locked

This value can be caused by any of the following situations:

• The record has been locked by another user and Microsoft Access cannot read the data.
• There are two or more instances of Microsoft Access running on the same computer. Microsoft 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.