Queries and Built-In Functions

In previous lessons, we saw that Microsoft Access was equipped with an impressive library of functions. These functions can also be used in queries and even included in SQL statements. The SQL interpreter of Microsoft Access can recognize these functions as long as you use them appropriately.

There are two primary ways you can include a function in a query. If you are visually building the query in the Select Query window, you can write an expression that includes the function. Consider the following list of employees:

Imagine that you want to create a column in a query and that column should hold the full name of each employee. In a column of a table, you could use an expression such as:

Employee: [FirstName] & " " & [MiddleName] & " " & [LastName]

The equivalent SQL statement is:

SELECT Employees.EmployeeID, 
       [FirstName] & " " & [MiddleName] & " " & [LastName] AS Employee
FROM Employees;

This would produce:

Notice that some employees don't have a middle name in their record and the field includes an extra useless empty space. Imagine that you only want to include a middle initial instead of the whole middle name. You can use the Left$ function to retrieve the first character of the middle name and include the call to that function in your query. To do this visually, if you are creating the query in the Select Query window, simply enter the function call where the column name would be. For our scenario, column name would be set as follows:

Employee: [FirstName] & " " & Left([MiddleName],1) & " " & [LastName]

This would produce:

Once again notice that some records don't have a middle initial because they don't have a name. Then, the middle name of some records was entered in lowercase. To convert a string to uppercase, we saw that we could use the LCase$ function. Here is an example:

Employee: [FirstName] & " " & UCase(Left([MiddleName],1)) & " " & [LastName]

For the records that don't display a middle name, we can write a conditional statement, using the IIf() function, to check it and taking the appropriate action accordingly. Here is the result:

This would produce:

In the same way, you can use any of the built-in functions we reviewed in Lesson 14.

Queries and Data Entry

As done for a table, data can be entered in a query. Before doing this, you must first create the query and, after creating it. Display it in the Datasheet View and enter the values in the cells. Only the columns that come from a table (or an existing query) can receive values. A column that was created from an expression cannot be edited since the database engine takes care of updating it using the entered data.

When data is entered into a query, its original table is automatically updated. This means that you can create new records intended for a table but using a query.