Can you summarize your design process

Summarize text values ​​using an expression

If you want to combine values ​​in two or more text boxes in Access, you can use the ampersand (&For example, suppose you have a form called Employees. You enter the first and last name of each employee in separate fields, but you want to display the employee's full name in the form header.

To see the full name, you can use the following expression:

= [First name] & "" & [last name]

The operator & used to combine the values ​​in the First Name and Last Name fields. A pair of quotation marks (") separated by a space to insert a space between the first and last name. If you want to insert something between two fields, such as a space, punctuation mark, or text, enclose that additional value in quotation marks.

Another example: You can use the following expression to display the last name and first name separated by a comma and a space:

= [Last name] & "," & [first name]

In this case, the expression inserts a comma and a space enclosed in quotation marks between the "Last Name" and "First Name" fields.

The steps in the following procedure assume that you have a form based on a table that contains the First Name and Last Name fields, otherwise you can change the expression in step 6 to suit your needs .

Insert a text box with a full name expression

  1. In the Navigation Pane, right-click the form or report you want to change, and then click on the shortcut menu Draft view.

  2. Click the tab design in the group Controls on Text field.

  3. Drag the pointer on the form or report to create the text box.

  4. Right-click the text box, and then click on the shortcut menu properties.

  5. In the property sheet, click the tab Data.

  6. Change the value in the property box Control content in = [First name] & "" & [last name].

  7. Close the property sheet and save your changes.

Sometimes one of the fields you are trying to combine does not contain a value. The lack of data is known as a null value. If you use the & for a field that does not contain a value, Access returns an empty string for that field. For example, if an employee's record contains only their last name, the expression in the previous example returns a zero-length string for the First Name field, then a space, and then the value in the Last Name field.

1. Since there is no data in the "First Name" field, "Martinez" is preceded by a zero-length string and a space.

When combining values ​​from multiple fields into a new string, you may want to include a value (like a comma) in the new string only if there is data in a particular field. To conditionally include a value, you can use the operator + instead of the operator & use to combine your fields. For example, suppose you have a table called Customers and that table contains City, State, and Zip Code fields. You want to combine the values ​​in these fields for a report, but some records do not have a value in the State field. In this case you will get an unwanted comma in front of the value "ZIP Code" when you use the operator & use to combine fields.

To avoid the unwanted comma, you can use the plus operator (+) as shown in the following example expression:

= ([City] & ("," + [State]) & "" & [Postcode])

The operator + combines text in the same way as the operator &. However, the operator supports + also a process that is called Zero passing referred to as. Null propagation ensures that if any component of the expression is zero, the result of the entire expression is also zero. In the example above, consider the part ("," + [State]) of expression. Since the operator + is used, the expression that is evaluated within the inner brackets contains a comma only if there is a value in the State field. If there is no value in the State field, the null propagation takes effect and the expression in the inner brackets evaluates to a null value, which "hides" the comma.

1. The records that contain values ​​for federal states are displayed with a comma, a space, and the state abbreviation.

2. If no state is specified in the data record with the value "Frankfurt" in the "City" field, the result of the expression is displayed without commas, spaces or the abbreviation of the state.

Top of page