Remove the Default Value to "0" permanently

  • Thread starter Thread starter Access Joe
  • Start date Start date
A

Access Joe

Hi!

Does anyone know of a way to permanently stop the Default Value of a table
from defaulting to 'ZERO' for Number data types? Of course, I can manually
remove them, but sometimes I forget - and as a result, averages are skewed
with summary calculations.

Can I permanently modify Access so when I choose Number data types, it
defaults to a 'blank' value instead of a Zero?

Thanks!
 
In Table Design, remove 0 from the DefaultValue property of that field
(i.e., leave the property blank).

Do the same thing to any existing form that has a control for that field as
well (newly created controls will reflect the change you just made to Table
design, but existing ones will not).

I'm assuming that the Required property for this field is already set to No
since you say "...I can manually
remove them...".
 
Sorry, just re-read and realized that you were asking for a means to make a
permanent change to Access' default behaviour. Afaik, there isn't a way.
When you create a table field for any Numeric data type, the DefaultValue
will always be set to zero. If you don't want it to be zero, you need to
change it yourself.
 
I don't believe so (although I believe the behaviour has changed in Access
2007). Why not write a routine to loop through all of the tables and reset
the default? Something like the following untested air-code:

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim fldCurr As DAO.Field

Set dbCurr = CurrentDB()
For Each tdfCurr In CurrentDb().TableDefs
If (tdfCurr.Attributes And dbSystemObject) = 0 Then
For Each fldCurr In tdfCurr.Fields
If fldCurr.DefaultValue = "0" Then
fldCurr.DefaultValue = vbNullString
End If
Next fldCurr
End If
Next tdfCurr
 
Can I permanently modify Access so when I choose Number data types, it
defaults to a 'blank' value instead of a Zero?
Click to expand...

That would be wonderful, but AFAIK there is no setting to do so.

I have a little VBA routine which loops through the tables collection and the
fields collection, setting the Subdatasheet of each table to None and the
Default Value property to blank where it is 0.
 
Back
Top