Getting rid of zeros in a column of a table

[Mojo1]

I have an Access table that has zeros in the column. Is there a way if a record has a zero for a specific column to make it blank rather than have a zero in it?

 

[gator]

If the data type is numeric, you can simply delete the zero under default value.

 

[jfgambit]

If you want to do this in a query add the following to a new row:
Expr1: iif([FieldName] = 0,Null,[FieldName])

Or you can create an update query to change all the values that are 0 to Null

HTH

 

[Mojo1]

Made datatype number

I changerd the data type to number and deleted the 0 from the default value, but it still shows 0 in the column.

 

[jfgambit]

You need to run an update query to remove all the existing 0's.

 

[Mojo1]

Update Query

Column Name is CurrentCharge.
I made an update query using the table this field is located in. I have selected only this field with iif([FieldName] = 0,Null,[FieldName]) in the criteria. Do you want me to run this?

Is there some way of using a validation rule or input mask to eliminate zero's??

 

[Mojo1]

Ran the query

I ran the query and it eliminated all the zeros in tha table. Now I have some other columns with numbers for records and zeros for records within a certain field. Will the update query leave those records with numbers in them?

 

[jfgambit]

Not sure what you mean by that. If you want to change the value in a table (for example 0 to blank), you create an update query, add the field you want to update to the columns are, in the Update to Filed add NULL in the Criteria filed add 0 and run the query, you should get a respone to the effect of "You are about to change XX number of records..."

If you want to change other fields you need to add them to the query and make the appropriate updates.

HTH

 

[Mojo1]

Check this out

I ahve this: IIf(HOTLINE_NUM]=0,Null,[HOTLINE_NUM])
in the update to: and it completely wipes every column out. There should numbers in some records that should be left but it takes them all out. I used this update quesry like you suggested on other columns and it worked great but not with this column for some reason.

 

[jfgambit]

The iif statement is supposed to be used if you want to preserve the data and just see Null in cases where there is a 0. you shouldn't use this in an update query:

ORIGINAL POST:
If you want to do this in a query add the following to a new row:
Expr1: iif([FieldName] = 0,Null,[FieldName])

Or you can create an update query to change all the values that are 0 to Null

Sorry if I confused you.