convert zero to NULL

[deso]

I need to convert zero values to NULL values (yes, I definitely need to do it!).

I am trying



INSERT TABLEA.COLUMN1 = IIf([TABLEA.COLUMN1]=0,NULL)

but this doesn't work. Any ideas?

[apr pillai]

You need an Update Query, rather than an Append Query, for what you are trying to do. You are trying to change all existing 0 values in COLUMN1 to Null. Use the following sample code:

UPDATE TABLEA SET TABLEA.COLUMN1 = NULL
WHERE (((TABLEA.COLUMN1)=0));

If you are trying to APPEND records and want NULL value as default in COLUMN1 then display the structure of TABLEA and set COLUMN1's DEFAULT VALUE property to NULL.

[deso]

Thanks for offering to help apr pillai, however, that doesn't work. I get "data type mismatch in criteria expression". I can't find any errors/mismatch in the SQL. Any ideas?

[ssanfu]

Post your SQL. It worked for me.

My test SQL

UPDATE Calcs SET Calcs.Contract_ID = Null
WHERE (((Calcs.Contract_ID)=1));

[deso]

Hi ssanfu, I literally used apr pillai's SQL:

UPDATE TABLEA SET TABLEA.COLUMN1 = NULL
WHERE (((TABLEA.COLUMN1)=0));

I was having problems when i converted that SQL into one that suited my table, so I copied the table, changed its name to TABLEA and the relevant column to COLUMN1. A box pops up first to ask me for parameter...I typed NULL, and get the mismatch error. But when I was converting the SQL to use in the original table, i was getting the mismatch error also. The field type of the column is text...is that a problem? If i convert it to number in datasheet view the values completely change.

[ssanfu]

If the field is text, then you need to use the proper delimiters.
A number zero (0) is not the same as a text zero ("0").

You shouldn't get a dialog box asking for a parameter. It means something is misspelled... check your field names.

Try this SQL

UPDATE TABLEA SET TABLEA.COLUMN1 = NULL
WHERE (((TABLEA.COLUMN1)="0"));

[deso]

There's no spelling errors i can see. I've resaved the tables, started with fresh SQL. I'm using this SQL:

UPDATE 4_GEO_LITHO_BORES SET 4_GEO_LITHO_BORES.TOP = NULL
WHERE (((4_GEO_LITHO_BORES.TOP)="0"));

4_GEO_LITHO_BORES is the table name, and "TOP" is the column name.

I'm getting this error: syntax error in query expression (((4_GEO_LITHO_BORES.TOP)="0"))

[deso]

guys, i've sorted this out with excel. I think there was an issue with the field values being text. so i converted to cvs., then converted cells to numbers, and sorted in excel and put it back in access. thanks anyway.