You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the United Kingdom:. This example changes values in the ReportsTo field to 5 for all employee records that currently have ReportsTo values of 2. Skip to main content. Each table appears in a window in the query designer.
In most cases, Access automatically joins related fields in a query. To manually join fields that contain related information, drag the related field from one table to the equivalent field in the other table. Access creates a relationship between those fields in the two tables and uses that relationship to join any related records. In the destination table, double-click the fields that you want to update. Each field appears in the Field row in the query design grid.
If you use the sample tables, you add all fields except the Customer ID field. Notice that the name of the destination table appears in the Table row of the design grid. In the Update To row of the query, in each of the columns that contains a destination field, add the name of the source table and the field in the source table that corresponds to the field in the destination table, and make sure that you use this syntax: [ Table ].
This figure shows part of the design grid, using the sample tables. Note the syntax for the table and field names in the Update To row. As you continue, remember that you must spell the table and field names in the Update To row correctly, and you must match any punctuation in the original table and field names.
However, you do not have to match capitalization. The following table lists the data types that Access provides, explains any restrictions on how to convert data types, and briefly describes any data loss that might occur during conversion.
Text must consist of numbers, valid currency, and decimal separators. The number of characters in the Text field must fall within the size set for the Number field. The Memo field must contain only text and valid currency and decimal separators.
The number of characters in the Memo field must fall within the size set for the Number field. Values must not be larger or smaller than what the new field size can store. Changing precision might cause Access to round some values. The dates that you can convert depend on the size of the number field. Remember that Access stores all dates as serial dates, and it stores the date values as double-precision floating integers.
Access uses December 30, as date 0. Dates outside the range April 18, and September 11, exceed the size of a Byte field. Dates outside the range April 13, and September 16, exceed the size of an Integer field. To accommodate all possible dates, set the Field Size property of your Number field to Long Integer or greater.
Values must not exceed or fall below the size limit set for the field. For example, you can convert a Currency field to an Integer field only when those values are greater than and do not exceed 32, The value -1 Yes converts to December 29, The value 0 No converts to midnight AM.
If the original text contains a valid Web address, such as adatum. Access tries to convert other values, meaning that you see underlined text, and the cursor changes when you point at the link, but the links don't work.
Not allowed when a Number field is part of a relationship. If the original value is in the form of a valid Internet Protocol IP address four number triplets separated by a period: nnn. Not allowed when the AutoNumber field is part of a relationship. The resulting links do not work. If you try to run an action query and it seems like nothing occurs, check the Access status bar for the following message:. By default, Access disables all action queries update, append, delete, or make table queries unless your database is in a trusted location or the database is signed and trusted.
If you have not taken either of these actions, you can still enable the query for the current database session by clicking Enable Content on the Message Bar. An expression that determines the value to be inserted into a particular field in the updated records.
An expression that determines which records will be updated. Understanding and using Update Queries improves the performance of your applications versus doing the same changes manually or in code , and makes them easier to maintain. Update Queries let you modify the values of a field or fields in a table. You can specify the records to modify by applying a filter WHERE clause and linking the table to other tables and queries.
UPDATE is useful when you want to change many records or when the records that you want to change are in multiple tables. You can change several fields at the same time. The following example increases the Order Amount values by 10 percent and the Freight values by 3 percent for shippers in the US:. In this example, all invoices with a balance due that are 30 days late have the LateStatus field updated to True.
Each contact may have a different value in its Contacted field depending on how many times it was contacted in the past. In this example, the address records are updated with the City and State value from a zip code lookup table. If the record has a zip code that matches the lookup table, its City and State fields are replaced. Any built-in VBA function that returns a value can be used as the update value.
Any public function that you create in a standard module can be used in a query. Your field is updated with the return value of the function. If your function takes parameters, you can pass field values as parameters and the function executes for each record that is processed. If an Update Query fails to modify the table's data, the first thing to verify is that the underlying table is updateable. Simply open the table and manually try to edit the field.
If you can't do it manually, the query can't make the change either. This can be due to several reasons:. Assuming you can edit your table, your query may fail and display this error message when you run it:. Operation Failed. If so, check out our paper on Error Operation must use an updatable query: Dealing with Non-Updateable Queries and the Use of Temporary Tables in Microsoft Access which discusses some of the issues and workarounds. They are extremely powerful and eliminate a lot of manual updates and unnecessary coding.
Note that this field does not actually exist in the Northwind database Employees table. This example selects the LastName and FirstName fields of each record in which the last name is King. Have questions or feedback about Office VBA or this documentation?
Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. Skip to main content. This browser is no longer supported.
0コメント