Convert zeros to null

[rmatos95]

I have a table where, in many fields there are numbers from 0 to 5. I need to convert the zeros into null. I guess I need to create an append query, but I do not know which expression to use so that it will convert the zeros to null.
Thanks for the help!

[gershwyn]

If you have a table with the data you want already in it, you should use an update query rather than an append. You can change a single field easily by using a where clause:

UPDATE [myTable] SET [myField] = Null WHERE [myField] = 0;

It sounds like you have multiple fields though. If you wanted to change multiple fields at the same time, you could use something like this:

UPDATE [myTable] SET
[myFirstField] = IIF([myFirstField]=0, Null, [myFirstField]),
[mySecondField] = IIF([mySecondField]=0, Null, [mySecondField]),
[myThirdField] = IIF([myThirdField]=0, Null, [myThirdField]);

This would examine the value in each field, setting it to Null if it is zero, and "updating" it to itself (ie, making no changes) it it's not zero.

If you don't have a lot of fields and this is something you only need to do once, you could write and execute the first query several times, once for each fields. (I sometimes do this to make formatting changes with imported data.) The second approach is a little more complex, but can be repeated when necessary.