Microsoft Access 2010 Bible

Chapter 13: Accessing Data with VBA Code

477

When you build a query using the Query Designer, Access automatically includes the table’s name

before the field name even when the table name is optional. The table name is required only when

more than one table in the SQL statement has fields with exactly the same name. For example, a field

named Invoice Number appears in both the Sales and Sales Line Items tables. If you want to

select an invoice number field in your SQL statement, you must specify which of these to use —

the one in Sales or the one in Sales Line Items.

The following SQL SELECT statement illustrates how the table name is used to specify which table

supplies Invoice Number:

SELECT tblCustomers.Company, tblCustomers.City, _

tblSales.SaleDate, tblSales.InvoiceNumber

Tip
Although table names are not required for non-duplicate fields in a SQL statement, it’s a good idea to use them for
clarity. Anyone viewing your SQL statements will immediately know where each field is found in the database.

You can use the asterisk wildcard (*) to specify that all fields in a table should be selected. If you’re

going to select all fields from more than one table, include the table name, a period (.), and the

asterisk for each table:

SELECT tblCustomers.*, tblCustomers.*, tblSales.*, tblSales.*

Caution
Generally speaking, it isn’t a good idea to use the asterisk to select all fields within a table. Your queries are
guaranteed to run more slowly than necessary if you routinely extract more data than needed in your queries.
By all means, select all the fields that are necessary to satisfy the user’s requirements, but don’t make a habit of
selecting all columns from all tables. Keep in mind that queries pull everything specified by the SQL statement,
regardless of whether a query’s data is displayed on a form or report.

A field or table name that contains spaces requires the use of brackets ([]). The brackets serve as delim-

iters to let the SQL parser know that you’re referring to a specific field or table. A field or table name

that contains special characters requires the use of brackets. (Spaces are considered special characters.)

If the name doesn’t contain spaces or special characters, you don’t need to use brackets. Access may

insert brackets around field and table names, but they’re generally unnecessary.

The square brackets surround just the field name (tblMyTable.[My Field Name]), not the table and

field name ([tblMyTable.My Field Name]). Think of the square brackets as marking the beginning

and end of an identifier.

Using brackets around field names