It appears Access sets the existing row values for the field to 0
Right, I forgot about that little detail. Over the years the MS folks have vacillated between defaulting to 0 or null for numeric fields. They have currently settled on defaulting to 0. This helps novices who don't understand how to handle nulls but is logically wrong in most cases and actually wrong in some.
1. Long Integers are primarily used as foreign keys. It is WRONG to allow FKs to default to 0. They need to default to null. Access can't tell the purpose of a field so it has no way to determine this at design time. By the time you create RI, it is too late.
2. Even though logically integers may be 0, it is better to default them to null but make them required so that the user is forced to enter a value. This is important when it comes to math, averages in particular. If the integer is for a test score and the test records are entered as a batch before the test is taken and the teacher then enters the scores after the fact, you have a situation where a student didn't take the test and if the teacher didn't remove the 0 default, the student ends up getting yelled at by his mother because his class average dropped because the average of (80, null, 90) = 85 but (80, 0, 90) = 56!!!! The point is ZERO has meaning.
Here's a tool that will help clean up tables after you design them
This database has several useful code procedures that you can copy into your BE to fix up defaults you don't like: 1. Change the allow ZLS string property to No If you want to make a text field required, it makes no sense at all to have the Allow ZLS property set to Yes. 2. Turn off sub...
www.access-programmers.co.uk
Just changing the default does not change the value of existing data so you need to use this code before the table is populated. Otherwise, you need to create update queries to change 0 to null or null to 0 depending on what you want to do.