transform zero values into null (blank)

[mar7632]

Hi everyone,



How do I transform zero values = 0 into null values automatically?

For example: I introduce in my field called age -> 0 *automatically age becomes "null" (in blank)

I tried to open the table in Design View and use Format located in the Field Properties.

Unfortunately, this "simple" task is not so easy to do.

Any ideas?


Thanks in advance.

[isladogs]

Remove the default value of 0 from that number field in design view.

[mar7632]

The point is that I want to avoid to introduce zero values in my form.

If Any value is introduced as zero = 0, then it shows null or blank

[Minty]

If it is a number field it can't be blank "" or ZLS , it can only be 0 or Null.

As Colin pointed out if you remove the default value, then simply put validation on the forms to not allow 0 to be entered, (do it in the forms before update event.)

[mar7632]

Thank you for your response,

I want to set a rule in which zero values are equal to null. Zero values can be introduced in my form by my co workers. I know that you can put validation on the form to not allow 0 to be entered, but for my application it does not work.

[Minty]

I don't understand why that wouldn't work. If you prevent them entering 0 then you avoid the problem, how can that not work?

The other route would be to use a combo box with all possible number values without 0 and use that as a control, but it seems overkill and messy especially if your values could be over a very large range of inputs ?

In the after update event a simple check of

If Me.MyControl = 0 then Me.MyControl = Null

would probably work, but confuse the hell out of the end user...

[CJ_London]

If Any value is introduced as zero = 0, then it shows null or blank

your requirement isn't very clear. Do you want to 'show' a blank for zero in your form? or do you want to store nulls instead of zeros in the table
have you considered using the format property? for numbers you can have 4 different formats

positive;negative;zero;null

so
0;0;""

will show a 'blank' for 0 values (but will store a zero in the table)

[mar7632]

Sorry, I know that the information I am providing is poor. But it's a long story haha.

Thank you for all your comments, specially Ajax's comment which is the key point and what I want from the beginning.

[mar7632]

I´ve been updating for each field I have in my table this format 0;0;"" But only it works with date and number fields. How do I set this property in text fields?

[isladogs]

Perhaps I'm being dense but why can't you use one of the setups below?

[mar7632]

The reason why I can not use the setup you show is that most of the data I Introduce in my access database is by using an export from excel which I receive from my cowokers. Some of the values from excel (which I am not allow to edit) are zeros. For me it is a little bit annoying to perform a update query every week, so I prefer to set this function in a easy way without using too much code.

[CJ_London]

How do I set this property in text fields?

text fields only have two format conditions - any text value or null. So not clear what you are trying to do. if you mean zeros in a text field, it won't work

see this link about the format property https://docs.microsoft.com/en-us/off...textbox.format

[isladogs]

Rather than import your Excel values direct into your table, first import into a temporary or 'buffer' table and do your processing there to remove zeroes, THEN import to your final table

[davegri]

Some of the values from excel (which I am not allow to edit) are zeros.

Colin's suggestion is what I like. Indeed you cannot edit a linked spreadsheet, but you can edit an appended table to your heart's content.

[mar7632]

Hi everyone,

Thank you for all your comments and sorry for not answering before. How do I import into a temporary "buffer" table? What are the steps?