Microsoft Office Tutorials and References
In Depth Information
Updating a column based on an expression
Updating a column based on an expression
When working with action queries, you can use the data in
other columns in the same table or data linked from other
tables to change the data in the columns you want to edit.
Examples include removing extra spaces from columns,
splitting the data from a single column into multiple columns (for
example, first and last names extracted from a ContactName
column), or consolidating the data in several columns into a
single column.
When constructing expressions, you can use a wide variety of
built-in functions for manipulating data, such as the string
functions UCASE, LCASE, RIGHT, LEFT, MID, TRIM, and InStr. In our
example, we use functions to split a contact name into a first
and last name.
2
3
Update a column based on an
expression
1 To test your expressions, create a select query based on your table,
as follows:
FN: Left(Trim([ContactName]),InStr(Trim([ContactName]),” “))
and
LN: Mid(Trim([ContactName]),InStr(Trim([ContactName]),” “)
+1,Len(Trim([ContactName]))).
2 Change the view to Datasheet view to check that all of the names
are split as you are expecting, and then return to design view.
Click Update.
3
(continued on next page)
1
TIP The Trim function removes any leading or trailing spaces;
the Len function provides the length of the string; the Left
function extracts the left side of the string; and the Mid function
extracts the Mid part of the string. The Instr function is used to find the
position of the first space in the string.
Search JabSto ::




Custom Search