Errors

In a program as complex as Access, there are many ways to make a mistake!

What will happen?

• Access may refuse to accept your entry for a property or expression. Error message may help.
• Access may refuse to display the form or report at all. Error message may help.
• Access may display an error code instead of a value in some or all records.
• In Design View you may see a green triangle in the upper left corner of control
  
  An information button appears when you hover over such a control. It usually gives details about the problem. In the illustration, the equals sign (=) was left off at the beginning of the expression in the control.
   =[Full Unit] & " = " & Count([Last Name])
  The text is centered in the control so you cannot see that the equals sign is missing.
   

Error Codes

Access has a number of error codes that it can display instead of a value when something is wrong.

• #Error
  Access cannot evaluate the expression. There are a number of reasons possible.
  • Circular reference: Using a control's name in an expression that defines values for the same control
  • Aggregate functions: Field name mis-spelled or there are not enough arguments given
  • Default value in a table or form: Wrong DataType or FieldSize for the field
  • Query: Result of calculation is larger than the FieldSize allows
• #Name?
  The name that you typed for a source is not valid.
  • Mis-spelled name of field
  • Omitted the equals sign (=) in an expression in a form or report
  • Source has been deleted
• #Num!
  The value is too large to be stored in the field due to the DataType or FieldSize
• #Div/0!
  Calculation is trying to divide by zero. Check your expressions for calculations for the possibility of current AND future records resulting in division by zero.
• #Deleted
  The record has been deleted.
• #Locked
  Access keeps other users from editing records that you are editing. In addition, if you have multiple Access databases open at once on your computer, Access treats each one as a separate user. So you can only edit records from one database at a time. Also, Access locks out groups of records at a time, not just single records. How many at a time depends on the size of the records.

Error Message Boxes:

If your typing is not EXACTLY right, you may see one of several possible error messages. Some of these messages are helpful, but others just announce that there is a problem... somewhere! For most of these errors, Access refuses to accept your expression.

Invalid syntax
Probably a missing operator, punctuation, bracket ([), quote(") or ampersand (&), but there are many other mistakes that generate this error message.
    

Invalid bracketing 
At least this message actually points out where the error is!
    
Undefined function
Mis-spelling a function is a really big error!
 

Enclose function arguments in parentheses
Leaving off the first parenthesis generates this error.
 

Message: Invalid String
Access refuses to accept what you typed for the expression. Your expression probably left out a closing double-quote.

Wrong number of arguments
Missing a required part of a function
 

Missing a closing parenthesis, bracket, or vertical bar
Not so helpful!
 

Unexpected parameter dialog appears.

This is not a planned parameter dialog! It is caused by an error.

• A field that does not exist was used in an expression or set as the Control Source for a control
• A record is blank in the field that joins tables or queries

In this example, either the field name FullName is mis-typed or there is a record for which the query cannot calculate FullName.

Lessons Databases Appendix

~~  1 Cor. 10:31 ...whatever you do, do it all for the glory of God.  ~~

Last updated: 30 Apr 2012