Jan's Access Tables & Queries: Calculated Values

Putting together field values, and possibly additional text, into a single text string is called concatenating. In Access you normally use the ampersand operator (&) as the glue to concatenate fields with each other and with literal text. The rules for writing an expression are called the syntax for the expression.

Spaces & Punctuation: When you concatenate fields, you will usually want to include a space between the fields. Sometimes you need some punctuation between fields or after the last one, like a comma between last and first names [LastName]&", "&[FirstName] or a final period &".".

Don't forget the matching parts: It seems to be particularly easy to fail to type the second ampersand (&) or the second double-quote (") when including text or spaces in the middle of an expression, like the comma and space in the paragraph above: &", "& . If you leave one of these out, Access will produce an error message.

Creating a Calculated Field in a Query

To calculate a new field for a query, in the Query Design view, you should type a new name in the Field row, followed by a colon (:) and then the expression that calculates the value.

If you do not pick a name for the new field, Access will assign it a name. The first unnamed field will be named Expr1. The next is Expr2, and so on for each calculated field that has not been named.

Calculated controls: In the next project, Forms & Reports, you will learn how to use the same kind of expressions to create calculated controls for a form or report. However, you name the control in a different way and the expression must start with the equal sign (=) instead of with the new name and a colon.

More room for typing:
You can get more room for entering and editing your expression by opening either a Zoom window or the Expression Builder. (Right click in the Field row of the column and choose Zoom or Build...)

You can click the Font... button to get a dialog to change the font or its size to suit your own eyes. Bigger is often better!

The Expression Builder can help you construct complex expressions. It can show you the proper syntax for functions, like IIf or DatePart. It has lists of all available functions, tables, queries, and even some common expressions.

IIf: Conditional Expression

Sometimes you will want your query to show different results, depending on whether the record matches your criteria or not.

Problem: In the expression [FirstName]&" "&[MiddleName]&" "&[LastName], if the MiddleName field is blank, then nothing will show for the MiddleName, but the two spaces on either side still show, like Whitney Green. One extra space may be a minor problem, but in other expressions you might wind up with several blank spaces.

Solution: Use a conditional expression (IIf) to show one value when the field is blank and another when it is not. Such an expression can look a lot more complicated than it really is!

Syntax for IIf (Immediate If):
The general form of an IIf statement has 3 parts.
IIf(expression, truepart, falsepart)

where expression contains the criteria that you want to evaluate. It must be either True or False.

PassOrFail: IIf([Grade]>69.5, "Pass", "Fail")
In words: For the new field PassOrFail, if the grade is greater than 69.5, show the word Pass. Otherwise, show the word Fail.

FullName: IIf([MiddleName] Is Null, [FirstName] & " " & [LastName], [FirstName] & " " & [MiddleName] & " " & [LastName])
In words: For the new field FullName, if there is no middle name, combine the first and last names with 1 space between them. Otherwise, combine first, middle, and last names with 1 space between each.

To find the parts of IIf expression: Look for the commas! (If only they were easier to see...!!!)

Access Text Functions

Step-by-Step: Calculated Value - Text

What you will learn:

to copy and rename a query
to create a new field by concatenating text fields
to use an IIf statement to define a new field
to use a query as source for a Lookup field
to change Lookup properties to match changed query
to view object dependencies

Start with: , Project database open.

The story so far:
World Travel Inc. has a new database with tables for Staff, Projects, and ProjectStaff.

There is a Lookup field in the ProjectStaff table for EmployeeID that would work better if it was based on a query that combined the three parts of a name into one field. Then your choice would show in a more useful way, as a whole name

Copy & Paste a Query

The query that you want to use for the Lookup field will be very similar to the one you just created in the last lesson, with the addition of a calculated field.

Happily, you can copy and paste database objects. You will have to pick a new name, of course. This can be a big help when what you want is similar to something that already exists. Making a few changes is easier than starting all over!

In the Database Window, right click on the query QSort Staff Names.
From the popup menu, select Copy .
The menu vanishes
Right click in a blank area of the Database Window.
From the popup menu, select Paste .
A small dialog appears for you to enter a name for the new object.
Type QStaff-FullName and click on OK.
The new query appears in the list.

Query Design: Concatenate Text Fields

Now you will create a new field to combine the name parts into one text string.

Open the query QStaff-FullName in Design View.
Scroll to the right, if necessary, to see a blank column.
In the Field row in the blank column type FullName:
This column is not wide enough to show all that you need to type this time. You need more space.
Right click on your typing and choose Zoom from the popup menu.
A new window appears where you can enter an expression to calculate a value for your new field.
Complete the expression for FullName as:
FullName: [FirstName] & " " & [MiddleName] & " " & [LastName]
Click on OK to accept the new expression and close the Zoom window.
You return to Query Design View with the field cell filled in with your new expression. It's all there even though the column is too narrow to show it all.
Run the query.

Errors: 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! List of common errors

Problem: Values in FullName column are cut off in datasheet.
Solution: You can scroll to see all of the FullName column and then widen the column to see the complete name.

Size column to fit: Double-clicking the right edge of the column heading will widen the column to show the widest name currently on the screen. This is not necessarily the widest name in the whole datasheet.

Extra spaces: Look at the FullName value for Hector Chavez and for Juanita Gonzales. There are extra spaces in the middle of the names because these two did not have a middle name.

Next you will learn how to avoid this kind of spacing problem. The solution looks more complicated than it is!

Query Design: Using IIf

To avoid extra spaces in the new FullName field, you need a way to concatenate the text one way when there is a middle name and a different way when there is not.

The IIf function described above is exactly what you need.

Switch back to Query Design View by clicking the View button .
Open the Zoom window for the FullName definition.
(Right click on the expression and choose Zoom)
Edit the expression to read:
FullName: IIf([MiddleName] Is Null,[FirstName] & " " & [LastName],[FirstName] & " " & [MiddleName] & " " & [LastName])

Remember that the IIF function has three parts - criteria expression, truepart, falsepart.
Look for the commas that separate the parts!

So what you typed means:
Expression: Is MiddleName Null?
Truepart: If so, use just the first and last names with a single space between them.
Falsepart: If MiddleName is not Null, use all three name parts, with a space between each part.
Run the query.
Now there are no extra spaces in the names!

Problem: Error message- Undefined function
Cause: Extra spaces or mis-typing. Be sure that there is only one space between the colon (:) and the expression and that you have typed all of the commas, ampersands, parentheses, and brackets correctly.
Close the query. When prompted, save the query.

The IIF function can be included in the middle of an expression also. For example, you can generate the same FullName value with [FirstName] & " " & IIf([MiddleName] Is Null,"",[MiddleName] & " ") & [LastName]

Table Design: Change Lookup Row Source

This query is now ready for work! You can make it the source for the Lookup field you created a couple of lessons back. You will have to change some other properties in the Table Design View.

If necessary, switch to the Tables in the Database Window.
Open the ProjectStaff table in Table Design View.
Select the field EmployeeID.
Click on the Lookup tab, if necessary.
The Row Source is the table Staff.
Click in the Row Source box and then on the arrow that appears at the end of the box.
A list of tables and queries appears.

Can you tell a query from a table in this list? You can if you started the queries name with a Q!
Select the query QStaff-FullName.
A button appears at the left, Property Update Options.
Hover over the button until a down arrow appears, then click the arrow.
A list of options appears.
A handy choice is to update all of the lookup properties that use this field, EmployeeID. If you had a lot of tables and you had just created a more useful query to use for a lookup field, this choice would save you a lot of effort.
Click on Update all lookup properties...
A message box appears telling you that nothing needed to be updated. That is because there are no other Lookup fields in other tables that used EmployeeID. Oh well. Perhaps next time this feature will be more useful!

Table Design: Change Other Properties of Lookup Field

Your goal is to show the new FullName field while storing a number value for the EmployeeID number. That way you won't have to try to remember which number goes with which staff member. The columns for the query are different from the columns for the original row source.

Change the Column Widths to 0" except for the 6th column which is the FullName field. That should have a width of 1".
Change the List Width to 2".
With each change that options box appears so that you can update other lookup fields, if there are any that use this same field.
Save the table and switch to Table Datasheet View.
The EmployeeID column now shows the FullName values but stores the EmployeeID number. The column is not wide enough to see the complete FullName.
Double click the right edge of the EmployeeID column to the right. It widens to show the widest name in the column, but only for the visible rows.
Click in the first row on Luis P Perez and then on the down arrow that appears.

This is your Lookup list! It is alphabetized by last name.
Why is this better than before? If there are two people with the same last name, your datasheet will now make more sense. It shows the complete name!
Close the table.

Object Dependencies

Your database is getting more complicated. The three tables have a relationship, so they depend on each other. The ProjectStaff table depends on the new query, QStaff-FullName as a Lookup source. Changes to these objects may break some of these connections.

Access 2003 has a new feature called Object Dependencies to help you track what depends on what.

In the Database Window, right click on the query QStaff-FullName.
From the popup menu select Object Dependencies .
The task pane at the right now shows some interesting information.
[This is the preferred method for bringing up the Object Dependencies task pane. It is hard to get Access to change to a different object with other methods.]

The first choice is the default one - Objects that depend on me.
The connections are simple this time.
Objects that depend on me:
Only the ProjectStaff table depends on the query.

If you want make changes to the query, look at the ProjectStaff table to see if you will break anything there with your changes.

Change Lookup query = Change Lookup properties
Be careful about changing a query that is used with a Lookup field. If you add or remove columns, you may need to change the Column Count and the Column Widths properties, as you did in this lesson.
Click on the expand button for the ProjectStaff table. The list expands to show what objects depend on that table. In this case, you see the related tables. You can continue expanding the lists up to 8 levels deep. You will see a lot of duplication of objects. Just remember that each expansion is showing objects that depend on the level above.
Click on the radio button for Objects that I depend on.
This query depends on only one object, the Staff table.

If you make changes to the Staff table, especially if you delete a field, you might break queries, forms, and reports that depend on that table.

Similar to the previous display, the expand button for the Staff table opens a list of objects on which this object (the Staff table) depends. This is very useful when you need to check to see if you are about to break something without knowing it!
Close the task pane.