Zeros in Access 2000

Topic

New Reply

I had an application in Access 97 that worked fine. A form
bound to a query that appended its contents (updateable)to
a table. If I left a text box blank, the updated table
showed a blank. In Access 2000, it insists on putting a
zero in the field. This table column is formatted
to “number”. I have tried ‘update’ queries to insert a
null value if there is not a value >0. No luck.

Any suggestions?

Thanks, Jim X6472

Reply | Quote

Replies

To start with, it’s poor practice to put nulls in numeric fields. Zero is the default value so that you don’t have nulls in there to bollix up calculations.

However, if this is a field you never use in calculations, open the target table in design view, select the field you want to append nulls to, and delete the zero from the default value property. Save the table, and it should allow null values.

Reply | Quote

Thank you, Charlotte for your prompt reply.
I looked at my table default and it didn’t have ‘anything’ in it. I agree about not using nulls in a numeric field becuase I am doing calculations; I just don’t want to see a zero if the field is blank. My frustration is caused by the fact it was working (not displaying zeros) before the upgrade. The rows we entered values into worked correctly; the rest were blank. It looks like Microsoft *improved* something I didn’t want fixed.
Thanks again, Jim

Reply | Quote

Well, I’m running SR-1a, and I don’t encounter that problem if I remove the default value from the table field. So I would check the form you’re using for data entry and see if there’s a default value in the control. If there is, it will stick a zero in even if there’s no default value set in the table.

Reply | Quote

In many cases it’s poor practice to put nulls in numeric fields, but I must butt in and say that there are cases where nulls are extremely useful in numeric fields! A previous database system that I used (4th Dimension on Macintoshes) had no provision for nulls and it was a royal pain in the a– to have to choose a particular “magic number” to use to signify missing data, remember to make sure it appeared blank in the output, make sure not to accidentally use it in calculations, etc. etc. In general, 0 and “missing” have radically different significance in most of the numeric fields I’m using in my databases.

Reply | Quote

It’s partly a matter of style. Yes, null and zero are not the same thing. However, if you’ve ever worked with millions of records in a database server, you realize what a pain Nulls really can be. The fact that a field is null doesn’t mean the data is missing, only that it’s missing from the field. However, even that depends on how the field is pulled, populated, manipulated, etc. I don’t allow nulls in numeric fields. If somebody wants a value to indicate that there’s something missing from the record, they have to do it a different way. The simplest way is not allow them to save the record without required fields being populated.

Reply | Quote

My appologies. I seem to have developed coding amnesia and forgot about a query that updated the afflicted fields. You’re right; it reset the field to *ZERO*. Arggg. I never would have found it if you hadn’t kept mentioning the default value.
Thanks, Thanks, Jim

Reply | Quote

You’re welcome! One of the problems with Access is that there are so many possible ways to do something, it’s sometimes hard to remember which ones you’ve checked.

Reply | Quote