Update Query

  • Thread starter Thread starter John Reynolds
  • Start date Start date
J

John Reynolds

I have a table with a field that needs to be updated.

I have the following update query:

UPDATE
SET Field1 = '5MH00'
WHERE Field1 = '5MH';

This works fine, but how do I get the update query to
update Field1 with various updates ie.

FROM TO
5MG 5MG00
5MT 5MT00
465 457

Do I have to create seperate update queries or can I merge
all these updates into one update query?
 
You may want to write a vba function to do this for you.
Here is an example, using your information below:

Function UpdateField1()
On Error Resume Next

Dim tbl As Recordset
Dim db As Database
Set db = CurrentDb
Set tbl = db.OpenRecordset("tblTest") 'This will be the
name of your table.

With tbl
tbl.MoveFirst
Do Until tbl.EOF
tbl.Edit
If Trim$(tbl!Field1) = "5MH" Then
tbl!Field1 = "5MH00"
ElseIf Trim$(tbl!Field1) = "5MT" Then
tbl!Field1 = "5MT00"
ElseIf Trim$(tbl!Field1) = "465" Then
tbl!Field1 = "457"
Else
tbl.MoveNext
End If
tbl.Update
tbl.MoveNext
Loop

End With
tbl.Close
db.Close

End Function


Depending on how many change possibilites there are, you
might want to do a Select Cast statement instead of this
if-then statement. You could create a macro that runs
this code, or just run the code itself. Since, I'm not
sure how often/when this needs to be run, that decision
would be up to you. I hope this helps.

Sandi
 
Back
Top