Putting Values Together (Concatenating)
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.
Syntax for an Expression that Concatenates Values:
Fieldname is between square brackets, like [Score]
Literal text and spaces are between quotes, like
"The score is "
Join the parts with:
Concatenating several text fields and spaces:
The expression [FirstName]&" "&[MiddleName]&" "&[LastName]
takes the 3 separate field values,
Whitney, R, and
Green and produces a single text string, Whitney R
Green, with a a single space between fields.
- Concatenating text with a number field:
The expression "The average is "&[Average]&"."
produces a text string like: The average is 78.
which includes a period at the end of the sentence.
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
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
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
Syntax for calculated field in a query:
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.
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...)
Zoom window is just a large blank area.
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!
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
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
The general form of an IIf
statement has 3 parts.
contains the criteria that you want to evaluate. It must be either True or False.
truepart is what you want to see when the expression is True.
falsepart is what you want to see when the expression is
Examples using IIf in a new query field:
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
Common typing error:
You can use IIf or IIF but not IFF!
Access Text Functions
Access has a number of built-in functions that can help you manage
Trim([City]) returns the value of the City field, but
without any leading or following spaces
Len([FullName]) counts the number of characters
in the field
UCase([State]) makes all characters in the field
LCase([ProductCode]) makes all characters in the field
Left([LastName],1) returns the first character from the left of the
This function is useful when you want to mark in a report where a list of names changes
from the A's to the B's etc. For other situations, you can use any
positive integer instead of the 1, to return that many characters.
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
The story so far:
World Travel Inc. has a new database with tables for
Staff, Projects, and
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.
the popup menu, select .
The menu vanishes
click in a blank area of the Database Window.
the popup menu, select .
A small dialog appears for you to enter a name for the new object.
QStaff-FullName and click on OK.
The new query appears in the list.
Concatenate Text Fields
Now you will create a new field to combine the name parts into one text
- Open the query QStaff-FullName
in Design View.
- Scroll to the right, if necessary, to see a blank
- In the Field row in the blank column type
This column is not wide enough to show all that you need to type
this time. You need more space.
click on your typing and choose 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]
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!
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
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.
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!
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
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,
Look for the commas that separate the parts!
So what you typed means:
Expression: Is MiddleName Null?
If so, use just the first and last names with a single space
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!
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.
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
& " " & 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
the ProjectStaff table in Table Design
- Select the field EmployeeID.
- Click on the Lookup tab, if necessary.
The Row Source is the table Staff.
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.
button appears at the left,
- Hover over the button until a down arrow appears, then
click the arrow.
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.
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.
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
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
- 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
Close the table.
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
the Database Window,
on the query
- From the popup menu select .
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
The first choice is the default one - Objects that depend on me.
The connections are simple this time.
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.
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
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.
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
Similar to the previous display, the expand button
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.