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)!

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

The TRIM function removes leading and trailing spaces from a string. Interestingly, the TRIM function in Excel removes extra spaces from within a string as well as leading and trailing spaces.

  1. Create a basic query which includes the field that you want to update.
  2. On the Query Tools » Design ribbon click on the Update button.
  3. In the design grid, in the Update To row, in the field which you want to update, create your calculation: 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!

Advanced: the above UPDATE query works nicely, but I noticed in one of my database fields that there were still leading and trailing spaces of some kind. It turns out that (as I copied the data from a webpage) there were trailing tab spaces. The Replace function finds a string within a string and replaces it with a string. In the below example, the function searches the field value for Chr(9), which is a tab, and replaces it with nothing. The resulting string is then used as the argument to the Trim function.

Trim(Replace([fieldname],Chr(9),""))

By MisterFoxOnline

CAT Educator

Leave a Reply

Your email address will not be published. Required fields are marked *

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