Access database query banner image.

To actually change the values in your table you can run an UPDATE Query against the table. You should always back your data up before running an UPDATE Query — there is no “undo” to return your data to its unchanged state (you could of course possibly run a second UPDATE Query to return it to its previous state — depending on what the first UPDATE actually did)!

In this post:

  1. Scenario
  2. Trim function
  3. Query

1. Scenario

Here is a handy UPDATE Query you could implement in your PAT database. The goal of this query is to “clean” your data of any extra spaces that may have crept in during the data capturing phase (users often add extra spaces at the end of words and phrases when entering data).

2. Trim function

The Trim function removes leading and trailing spaces from a string. Interestingly, Excel’s TRIM function removes extra spaces from within a string as well as leading and trailing spaces.

3. Query

Create a basic Query that includes the field(s) that you want to update, then complete the steps below with the help of this screenshot:

Screenshot of an UPDATE query in design view
  1. Activate the Query Tools » Design ribbon,
  2. click on the Update button to convert the Select Query to an Update Query,
  3. an Update To row now appears in the design grid,
  4. click in the Update To row, and
  5. type the expression: Trim([fieldname])

Remember: once you have saved the UPDATE Query, if you double-click the Query, it will run the Query, so you always need to be careful with it!

Running a Microsoft Access Update Query.Running a Microsoft Access Update Query.

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.