Update Query

[Dan @BCBS]

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

 

[Tim Ferguson]

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

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