Example of Errors caused by spaces in names and using reserved words

Question:

This is not a question as such, but a compilation of questions. I sometimes get questions about errors or strange behavior of the database, where the answer does not seem obvious.

Two of these examples are as per below (but there are many, many more):

When a subdatasheet is expanded to see the related records, you get what you expect. However, when the subdatasheet is sorted, the following error message appears, "You must use the same number of fields when you set the LinkChildFields and LinkMasterFields properties." Clicking the OK button, I get all the subdatasheet records instead of those only related to that record.

The second one was to do with strange results in calculations:

An unbound textbox has the following syntax:

=([length]*[width]*[depth])

The result is nothing like what is expected.

Answer:

The first problem is caused by spaces in the table name. The second is caused by using a reserved Microsoft Access word (or function).

Therefore, when developing a database, I suggest:

  • Not having spaces in anything. Instead of spaces use Capitals, i.e. ThisIsMyTable
  • Do not use Microsoft Access reserved words as table, form, query, report or field names.

For a list of reserved Microsoft Access words, see the following site:

http://allenbrowne.com/AppIssueBadWord.html

If you get a strange error or result, check the above as possible causes.