Jan's Illustrated Computer Literacy 101 Logo:Jegsworks Jan's Illustrated Computer Literacy 101


Home > Jan's CompLit 101 > Working with Databases > Tables & Queries > Queries > Calculated Text
Icon: Arrow - Previous pagePrevious    NextIcon: Arrow - Next page

Jan's Working with Databases

Tables & Queries: Queries: Calculated Text

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.

Icon: Confused smiley Point of Confusion: Calculated Field vs. Calculated Value
Access 2010, 2013, and 2016 have a Calculated data type. A field with this data type can use fields from the same table to calculate a value which is stored in the table. A query can pull fields from multiple tables to use in creating a calculated value but that value is not part of any table. So it must be calculated every time the query runs.

Expression

It is easy to write your own expression to calculate a new field in your query using other field values. That field is not part of any table.

The general form is:
   NewFieldName:expression

Example: Name: [LastName] & ", " & [FirstName] would produce values like Smith, Janet and Gomez, Luis.

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


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 and 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 the ampersands out, Access will produce an error message.

List of common errors Icon: In Site


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

Tip 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 will need to know what you want to type.

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! This only affects the Zoom dialog.
 

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]) gives the length of the value in the field FullName (number of characters)

  • UCase([State]) makes all characters in the field State upper case.

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

  • Left([LastName],1) returns the first character from the left of the field LastName.
    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, paste, 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 row source for a Lookup field
to change Lookup properties to match changed query
to view object dependencies

Start with: Class disk, Projects database (projects2-Lastname-Firstname.accdb) from previous lesson open.

The story so far: 
World Travel Inc. has a new database for information about projects that includes tables for Staff, Projects, and Project Staff.

There is a Lookup field in the Project Staff 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 instead of just the first name.

Copy, Paste, and Rename 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. Right Click Menu: CopyIn the Navigation Pane, right click on the query QSort Staff Names.
  2. From the context menu, select Copy .
    The menu vanishes but nothing seems to have changed.
     
  3. Right Click Menu- PasteRight click in a blank area of the Navigation Pane.
  4. From the context menu, select Paste .
    The Paste As dialog appears. You must create a name for the new object.Dialog: Paste As - QStaff-FullName The default name is Copy of QStaff Full Name.
     
  5. Navigation Pane: new query (Access 2016)Dialog: Paste As with new nameType QStaff-FullName and click on OK.
    The new query appears in the list.

    Next you will edit the query to create the calculated field for a complete name.
     


Query Design: Concatenate Text Fields

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

  1. Icon: Design View Query Design View: QStaff-FullName - blank columnOpen the query QStaff-FullName in Design View.
  2. Scroll to the right, if necessary, to see a blank column.
  3. Query Design View: QStaff-FullName - starting to create the calculated field (Access 2010)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 context 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.
    Icon: Datasheet View The new field shows as the last column.

    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 errorsIcon: In Site

    Icon: TroubleProblem: Values in FullName column are cut off in datasheet.
          Solution:
    You can 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 edit the field name to use an IIf expression 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 View Switch back to Query Design View.  
  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])

    Be sure to type in the final parenthesis!

    Zoom window for calculated field FullNameRemember 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. Query Datasheet View: FullName with no extra spacesIcon: Run Run the query.
    Icon: Datasheet View Now there are no extra spaces in the names!

    Message: Undefined functionIcon: TroubleProblem: Error message- Undefined function
    Cause:
    Mistyping the function name.
    Solution: Be sure that you have spelled the functions and field names correctly and that you typed all of the commas, ampersands, parentheses, and brackets that you need. The illustration shows that the f was left off the IIF function name. Previous versions of Access were even picky about spaces.  

    Icon: TroubleProblem: Still showing extra space
    Cause:
    Something is not typed correctly in your expression.
    Solution: Check spelling and capitalizations and punctuations.

  5. Button: Save (Access 2010) Close the query. When prompted, save the query.

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


Table Design: Use Query as Lookup Row Source

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

  1. Table Design View: Project StaffIcon: Design View Open the Project Staff table in Table Design View.  
  2. Click on the field EmployeeID.
    Do no use the Lookup Wizard this time. This table is already part of two relationships so an error message would tell you to delete those relationships first. Manually creatings the Lookup avoids this problem.
  3. Click on the Lookup tab, if necessary.
    The Row Source is the table Staff.

     
  4. 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! This is an excellent example of why it is helpful to make it obvious from the object's name what type of object it is.
  5. Select the query QStaff-FullName.
    A button appears at the left, Button: Property Update Options Property Update Options.
  6. 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.

    There are only two choices. One is to open a Help article and the other is to update all of the lookup properties in the database that use this field, EmployeeID. If you had several tables that use a Lookup list using this field, this choice would change all of them at once. A great help and time-saver!

  7. 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 so you must change some of the Lookup properties.

  1. Change 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. Button: Save (Access 2010) Save the table.

  4. Icon: Datasheet View Switch to Table Datasheet View.

    Table Datasheet View: lookup list for FullNameThe EmployeeID column will now show the FullName values but it still stores the EmployeeID number. The column is not wide enough to see the complete FullName.
     

  5. Table Datasheet View: lookup list for FullNameClick in the first row on Luis P Perez and then on the down arrow that appears in the cell.
    This is your Lookup list. It shows full names and is alphabetized by last name.

    Why is this better than the Lookup list before? The changes let you see the complete names in natural order. This is easier to read and avoids issues where only part of the name was visible.

    Notice the width of the lookup list compared to the width of the column. They are not related at all. This is why you set the list width to 2".

  6. Table Datasheet View: column widened to show full name (Access 2010) 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.
  7. Button: Save (Access 2010) Close and save the table.

View Object Dependencies

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

  1. In the Navigation Pane, click on the query QStaff-FullName.Task Pane: Object Dependencies - QStaff-FullName - depend on me
  2. In the Database Tools ribbon tab, click on the button Button: Object Dependencies (Access 2010) Object Dependencies .
    The objects that depend on the selected query show in the Object Dependencies task pane.

    Objects that depend on me:
    Only the Project Staff table depends on the query.

    Icon: TipTable depends on a query: The query is being used as a Lookup list for a field.

    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. Object Dependencies: QStaff-FullName - depend on meClick on the Button: Expand Button: Expand expand button for the Project Staff  table.
    The list expands to show what objects depend on that table. In this case, you see the related tables. Access limits the expansion 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.
     
  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.

    Warning Breaking Dependencies: 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!

  5. Button: Save (Access 2010) Close the task pane and all open objects.
    Save changes.