ACC: Troubleshooting Tips for Error Values
ID: Q112103
|
The information in this article applies to:
-
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")
In the above example, the table name should be "Employee," not "Employe."
Circular reference: (Query)
FirstName: [FirstName] & " " & [LastName]
In the above example, the alias FirstName is also part of the expression.
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 the above example, the text box name 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 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)
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, Microsoft
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!
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]
The following example demonstrates an invalid ControlSource property name:
ControlSource: =[FirstNam] & " " & [LastName]
Should be =[FirstName] & " " & [LastName]
When you are referring to a control on a subform or subreport in Microsoft
Access 2.0, you must refer to it through the main form or report using the
following syntax:
=Forms![<MainFormName>]![<SubFormName>].Form![<ControlName>]
-or-
=Reports![<MainReportName>]![<SubReportName>].Report![<ControlName>]
<MainFormName> or <MainReportName> is the name of the form or report that
contains the subform or subreport.
<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.
It is important to note that Microsoft Access uses page locking instead
of record locking. Microsoft 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 only requires 200 bytes, then up to 10 records
may be stored on a page. When Microsoft Access places a lock on a record,
it locks that record's page. If there are 10 records on the page, then all
10 records on that page are locked.
Additional query words:
pounderror poundname
Keywords : kbusage ExrOthr
Version : 2.0 7.0 97
Platform : WINDOWS
Issue type :
Last Reviewed: April 2, 1999