To actually change the values in the records 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:
Required knowledge:
Next steps:
Here is a handy UPDATE Query you could implement in your PAT database.
1. Scenario
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:
- Activate the Query Tools » Design ribbon,
- click on the Update button to convert the Select query to an Update query,
- an Update To row now appears in the design grid,
- click in the Update To row, and
- type the expression: Trim([fieldname])
4. Run!
Remember: an UPDATE query is an action query, if you double-click the query, it will run the query, so you must learn to always be careful when running it!
