Tables & Queries:
Calculated Values - Text

Title: Jan's Illustrated Computer Literacy 101


You can use a query to create a new text string by combining several field values, either text type or number type. You can even include fixed text. We call these calculated values, even when the new values are text instead of numbers. Such new fields are not added to the design of the underlying tables.

It is easy to write your own expressions to calculate a new field in your query using other field values. The general form is:
   NewFieldName:expression

Example: Query with calculated field

Calculated values are particularly useful in forms and reports. They allow you to control the spacing of long text strings and to write sentences or phrases that include a field value. This is similar to how companies create those "personalized" letters that use your name instead of "Dear Customer".


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing TablesTo subtopics
    Designing Queries Arrow: subtopic open
    Icon: StepSelect Query
    Icon: StepCalculated Values-Text
    Icon: StepCalculated Values-Numbers
    Icon: StepCalculated Values-Totals
    Icon: StepCalculated Values-Dates
    Icon: StepParameter Query
        Action Queries
    Icon: StepMake-Table Query
    Icon: StepAppend Query
    Icon: StepDelete Query
    Icon: StepUpdate Query
    Icon: StepCrosstab Query
    Icon: StepDocument a Query
    Summary
    Quiz
    ExercisesTo subtopics

Project 4: Forms & Reports


Search  
Glossary
  
Appendix



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:

    • the ampersand character (&), like  "The score is "&[Score]
         OR

    • the plus character (+), like "The score is "+[Score].
      But, if part of the expression is null and you used the + character, the whole expression will be null. This is called propagating nulls. Normally you do not want to propagate nulls. You want to see whatever is there!

Examples:

  • 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.

TipSpaces & 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 &".".

TipDon'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.

List of common errors


Creating a Calculated Field in a Query

Example: Query that calculates a valueTo 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.

Syntax for calculated field in a query:
    NewFieldName: Expression

Example:  StudentName:[LastName]&", "&[FirstName]

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.

TipCalculated 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...)

Zoom windowThe 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 BuilderThe 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.

For example...

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.

              truepart  is what you want to see when the expression is True.

              falsepart  is what you want to see when the expression is False.

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.

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

TipCommon 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 text.

Examples:

  • 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 upper case.

  • LCase([ProductCode]) makes all characters in the field lower case.

  • Left([LastName],1) returns the first character from the left of the field.
    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.


Icon: Step-by-Step 

Step-by-Step: Calculated Value - Text

 Icon: Step-by-Step

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:  Class disk, 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!

  1. In the Database Window, right click on the query QSort Staff Names.
     
  2. Right Click Menu: CopyFrom the popup menu, select  Copy .
    The menu vanishes
     
  3. Right Click Menu- PasteRight click in a blank area of the  Database Window.
     
  4. Dialog: Paste As - QStaff-FullNameFrom the popup menu, select  Paste .
    A small dialog appears for you to enter a name for the new object.
     
  5. Database Window: new query = QStaff-FullNameType 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.

  1. Open the query QStaff-FullName in Design View.
     
  2. Scroll to the right, if necessary, to see a blank column.

    Query Design View: QStaff-FullName - blank column

  3. 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.
     
  4. Right Click Menu: Field - ZoomRight 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.
     
  5. Complete the expression for FullName as:
    FullName: [FirstName] & " " & [MiddleName] & " " & [LastName]

    Zoom window for calculated field FullName

  6. New field FullName with expression to calculate its valueClick 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.
     
  7. Icon: Run Run the query.
     

    Query Datasheet View: QStaff-FullName

    Icon: TroubleErrors: 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

    Icon: TroubleProblem: 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.

    TipSize 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.

    Query Datasheet View: FullName with spacesTipExtra 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.

  1. Icon: Design Switch back to Query Design View by clicking the View button Button: View  - Design.
     
  2. Open the Zoom window for the FullName definition.
    (Right click on the expression and choose Zoom)
     
  3. 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.
     
  4. Icon: Run Run the query.
    Now there are no extra spaces in the names!

    Message: Undefined functionIcon: TroubleProblem: 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.
     
  5. Icon: Save Close the query. When prompted, save the query.

TipThe 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.

  1. If necessary, switch to the Tables in the Database Window.
     
  2. Table Design View: Project StaffOpen the ProjectStaff table in Table Design View.
     
  3. Select the field EmployeeID.
     
  4. Click on the Lookup tab, if necessary.
    The Row Source is the table Staff.
     
  5. Choosing a query as the Row Source for a Lookup field.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.
     
    TipCan you tell a query from a table in this list? You can if you started the queries name with a Q!
     
  6. Select the query QStaff-FullName.
    Button: Property Update OptionsA button appears at the left, Button: Property Update Options Property Update Options.
     
  7. Hover over the button until a down arrow appears, then click the arrow.
    Button: Property Update Options = Update all lookup properties everywhere EmployeeID is usedA 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.
     
  8. Message: No objects needed to be updated.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.

  1. Table Design View: ProjectStaff - new lookupChange the Column Widths to 0" except for the 6th column which is the FullName field. That should have a width of 1".
     
  2. 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.
     
  3. Icon: Save 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.
     
  4. Table Datasheet View: lookup list for FullNameDouble 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.
     
  5. 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!
     

  6. Close the table.

Icon: Access 2003 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.

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

  1. Task Pane: Object Dependencies - QStaff-FullName - depend on me Right Click Menu: Object Dependencies In the Database Window, right click on the query QStaff-FullName.
     
  2. 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.

    WarningChange 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.
     

  3. Click on the Button: Expand 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.

    Object Dependencies: QStaff-FullName - depend on me Object Dependencies: QStaff-FullName - depend on me


     

  4. Object Dependencies: QStaff-FullName - I depend onClick 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 Button: Expand 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!
     

  5. Close the task pane.