problem with sql update statement

Hi, I would like the update the blank records in a field, but with sequential numbers, from 13-2141. (ie: the first blank record would be updated with the number 13 and the last blank record with the number 2141)

How can I change the following sql stament to get the results I want:





UPDATE FISHSRY_SCCT_MAIN SET FISHSRY_SCCT_MAIN.Meta_data_ID = "meta_data_id +12"
WHERE (((FISHSRY_SCCT_MAIN.Meta_data_ID)=0 Or (FISHSRY_SCCT_MAIN.Meta_data_ID) Is Null));


Thanks for your time

Sue

one more thing

i just wanted to add that there are 2129 blank records in the field to be updated, just the exact amount I want to add

You cannot achieve what you desired with queries. But you can use VBA.

Open a new form in Design View. Place a command button on the form. Right-click on the command button and select Build Event... Choose Code Builder, click OK.

Paste the following code in the command button's Click Event:-

--------------------------------
Private Sub Command0_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim InitialNumber As Integer
Dim i As Integer

InitialNumber = 13

Set db = CurrentDb
Set rs = db.OpenRecordset("FISHSRY_SCCT_MAIN")

i = InitialNumber
Do While Not rs.EOF
If IsNull(rs!Meta_data_ID) Or rs!Meta_data_ID = 0 Then
rs.Edit
rs!Meta_data_ID = i
rs.Update
i = i + 1
End If
rs.MoveNext
Loop

MsgBox "Updated " & i - InitialNumber & " IDs"

Set rs = Nothing
Set db = Nothing

End Sub
--------------------------------

Save the form as any name you like. Open the form and click on the command button.

If everything runs fine, a message box will pop up telling you how many IDs have been updated.


Notice that the code was written in Access 97 so DAO was used. If you use Access 2000, you must make a reference to DAO (when the code window is open, choose menu Tools, References... check the Microsoft DAO 3.6 Object Library in the Available References list.)

Always back up your table before running any update code for the first time.

THANKS!

Thanks Jon!

Thanks for taking the time to answer my question, it worked!