Link to home
Start Free TrialLog in
Avatar of careybroph
careybrophFlag for United States of America

asked on

Turn off MS Access Default=0 for Numerics

Microsoft needs a slap on the hand. When creating a table through the Design interface, if you select a numeric data type, it automatically sets the Default to 0. NEVER... NEVER... should that be assumed. A zero is very different from a null. Wrong of MS to assume they know what is needed on any given project.

Okay, rant over.
Now, for my question... is there a way to turn this off??
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

what office version do you have?
afaik, the default value to a number field is nothing unless you placed 0 on it.
Avatar of careybroph

ASKER

Office Professional 2016. When I change a field type from the default (Short Text) to Numeric, it sets the default to 0. This is a typical install.
what about if you add a new number field, do you see a default value of 0?
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> sets the Default to 0. NEVER... NEVER... should that be assumed

Oh, that certainly depends.
But also I would prefer it being and adjustable global option you could set Null.

/gustav
Gusav,

With all due respect, the fact that you say it "depends" actually supports my position. You are 100% correct that it "depends". It depends on the intent and requirements of the field. MS doesn't default dates to 1/1/1980. It doesn't default Yes/No fields to No. It should not default numbers to "0" unless I specifically ask it to. It assumes that the user wants zeros (and actually understand the impact). Collecting "AgeInYears" in your import file? Sorry, all of your blanks were just changed to newborns. Collecting "SATEnglishScore"? Sorry... your imported data where students took the ACT instead of the SAT just has SAT scores set to zero. Hope you remember to filter those out when computing averages. Collecting weight gain/loss information? Sorry, you just changed imported data that changed all blanks to 0's (meaning their weight didn't change, which is not accurate).

No other database system makes assumptions like this. No other database system actually "creates" data not proved by the user. It is not the place of MS to assume anything about the data being collected, especially when it violates basic data integrity rules. I work at a pretty intense research university and this flies in the face of best practices.

This was actually fixed in an earlier version. Was very surprised to see it devolve in 2016.