Update/set blank field in table with value dependent on other fiel

[Guest]

I have a table with a blank NameID field. I was hoping to use an update query
to set the values in this field depending on what appeared in the Name field
i.e. along the lines of:

UPDATE tblMaster SET tblMaster.NameID = 1 if tblMaster.Name = "AAA" ELSE
= 2 if
"BBB" ELSE
3
"CCC"
etc. etc.

I don't seem to be having much luck with the syntax.
Thanks in advance for any help.

 

[Guest]

Try that, you should use the iif instead of IF.
UPDATE MyTable2 SET MyTable2.StartDate =
IIf([Name]="aaa",1,(IIf([name]="bbb",2,IIf([name]="ccc",3,4))));

But if you dont have alot of names and you want to run this query only once
then you can use an update query for each name at a time.

If you going to use this query alot then you should think of another
solution, like another table containing names and codes.