This isn't a MS problem (OK it is but it's not a bug). MS has vacillated with certain defaults over the years and has settled on two poor choices for recent versions.
For text fields, the default is set to Yes for AllowZeroLengthStrings. This is what you have run up against. I ALWAYS change this setting to No but changing the setting won't fix any bad data that has already crept in so you need to run an update query that selects SomeField = "" and then updates it to Null.
For Numeric fields, the default is set to 0. The problem here is that 0 has meaning. Setting currency or even single/double to 0 is probably OK but Long Integers are almost always foreign keys and 0 is invalid as a FK and so should not be used as the default. I use Null as the default for all numeric fields, not just long integers. An example of the issue caused by defaulting integers or long integer to 0 is a table that holds grades. Typically, when a teacher gives a test, he will run an append query that appends a row for each student linking to the text but with null as the grade. Then when he scores the test, he fills in the grades. However, here's what happens if the grade field defaults to 0 instead of null.
The average of 3,0,3 is 2 but the average of 3,null,3 is 3 so if he doesn't have a grade to enter yet for the test because a student wasn't present and needs a retake, is it messes up the student's average.
Here's a sample database that fixes some defaults. It also contains some other samples.
