Update Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to change text and number data types example,
Text = Y into -1 and all the N into 0.
Numeric = 1's into -1 and all the 2s into 0.

I assume I would start a new update query, add both tables, then what do I
put in Criteria and Update to lines????

Thanks
 
Text = Y into -1 and all the N into 0.
Numeric = 1's into -1 and all the 2s into 0.
Click to expand...

UPDATE MyTable
SET TextField = IIF(TextField="Y","-1",
IIF(TextField="N","0",NULL)),

NumField = IIF(NumField=1, -1,
IIF(NumField=2,0, NULL))



Not completely sure how these will work if there are nulls in the fields
already... might be wise to do them separately and put in a

WHERE TextField IS NOT NULL

(or NumField in the second one, obviously) to be on the safe side.
Alternatively, you can put in lots of NZ() functions.


If you want to do this in the query grid, you can build the nested IIF()
statements in the function builder.


Hope that helps


Tim F
 
Back
Top