I need to update 16 records of a table with a user defined serial number based on the value in a field. i.e If [Position]= 1 then[Enter Serial Number] and have it add [Serial Number]into all records into which [Serial Number] is currently null AND [Position] = 1. This is my first post and would greatly appreciate help as I have no idea how to accomplish this. Thanks in advance.

Open the QBE grid.
1) Add the table.
2) Select the Position field and the SerialNumber field.
3) Change the query type to Update.
4) Put - 1 - in the Criteria line of Position
5) Put - Is Null - in the Criteria line of SerialNumber
6) Put - [Enter Serial Number] - in the Update to line of SerialNumber

Thanks Pat - I wish it were that easy - need to give a little more detail here. An Update query with Parameters would be fine if it were for 1 case, unfortunately I need to update for 16 possibilities ie. if 1 then[Enter Serial Number] - if 2 then [Enter Serial Number] etc.I am trying to find a way to avoid 16 seperate queries and still get all records updated.Maybe an IF THEN with a loop? SQL or VBA?

Thanks again and would appreciate any suggestions.


[This message has been edited by Jerry Stoner (edited 11-01-2001).]

Create a table with two columns. One for the Position field and the other for the SerialNumber field. Populate the table with the values you want to change. Then instead of using specific criteria for the Position field, add the new table to the query and draw a join line to connect the position fields of each table. And instead of using the [Enter Serial Number] prompt, type a reference to the SerialNumber field in the new table:

[YourNewTableName].[SerialNumber]

Thank You Pat, that was embarassingly simple. I feel brain dead. Appreciate your time.