update query in access 2000

[Guest]

how do I run and update query?

 

[strive4peace]

Hi Pat,

here is how the SQL is structured for an update query:

~~~~~~~~~~~ UPDATE SQL ~~~~~~~~~~~
An Update Query first identifies the tables that are used

UPDATE table1 INNER JOIN table2 ON table1.keyfield =
table2.keyfield

Then identifies what to change

SET table1.fieldtochange = expression

Then, if you have criteria...

WHERE table.strField = 'somevalue'
AND table.numField = 99
AND table.dateField = #1/1/06#

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~`

if you want to use the query design view to make an update query, here
is a simple example:

~~~~~~~~~~~ Update Query ~~~~~~~~~~~

make a new query based on the table you want to change

change the query type from a Select Query to an Update Query
from the menu bar --> Query, Update

for instance, lets say you are storing just the mailbox name and want to
add to it so it is a fully qualified email address

field --> EmailAddress
UpdateTo --> TRIM([EmailAddress]) & "@anywhere.com"
Criteria --> not like "*@*"

then, RUN (!) your query

& specifies that a literal string will be added to the end of all your
email addresses that have no "@" in them

TRIM will chop off leading and trailing spaces from your data

not like "*@*"
means that only fields not already containing the @ symbol will be replaced

Don't forget to type the square brackets in the UpdateTo cell... Access
likes to put quotes around undelimited fieldnames -- then ALL your email
addresses would change to "(e-mail address removed)"

naturally, back up your database before running any action queries on it


'~~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access

 

[Joseph Meehan]

how do I run and update query?

Normally you click on the "!" button at the top of the page. How are
you trying to run it?

 

[Guest]

figured it out thanks

Normally you click on the "!" button at the top of the page. How are
you trying to run it?