Results 1 to 8 of 8
  1. #1
    Jimmy
    Guest

    Zeros in Access 2000

    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

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zeros in Access 2000

    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.
    Charlotte

  3. #3
    Jimmy
    Guest

    Re: Zeros in Access 2000

    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

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Zeros in Access 2000

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zeros in Access 2000

    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.
    Charlotte

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zeros in Access 2000

    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.
    Charlotte

  7. #7
    Jimmy
    Guest

    Re: Zeros in Access 2000

    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

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Zeros in Access 2000

    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. [img]/w3timages/icons/grin.gif[/img]
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •