A 2013: Why do all new number fields have a default of 0?

[XelaIrodavlas]

Hi all,

So this is really bugging me, I am using Access 2013 and in table design view. Whenever I add a new 'Number' or 'Currency' field the 'Default Value' property is (by default) always 0.

This is fine except when i want to refer to a foreign key or amount which could be unknown. In either of those cases the resulting field will either be invalid (because no foreign key has ID = 0) or inaccurate (because 0 is not the same as Unknown). This is really annoying.

I would prefer if the 'Default Value' property for new fields was always nothing, then I can add my own if and when required. Does anyone know if there's a way to do that?

Just to be crystal clear, I want to change the default setting on a property for new fields, not the value of the field itself.

I'm guessing this isn't actually possible, but at least this post has served to vent my frustrations! What bugs me even more is that i'm sure it wasn't like this in Access '10, is this another "upgrade", can anyone confirm this?

Thanks all,

Alex S

 

[Frothingslosh]

They're set to zero because numeric fields cannot store a null value - they have to have SOMETHING in them. (For the record, strings have the same issue - it just doesn't look like it because to human eyes, an empty string appears the same as no value at all.) The easiest workaround is to set a default that is an impossible value, and then have your forms display nothing at all if that is the value of the field. That said, it does create difficulties in editing that field directly if you do that.

 

[CJ_London]

They're set to zero because numeric fields cannot store a null value

I use 2010, rather than 2013. You can set the default to null in 2010 - has this feature been removed for 2013?

 

[ButtonMoon]

They're set to zero because numeric fields cannot store a null value - they have to have SOMETHING in them.

This is incorrect, at least in Access 2007 and I'm pretty sure in later and earlier versions too. In Jet/ACE, if a field is set to Required=No then if it's a numeric column it can contain nulls; if it's a string it can be null or empty. Null is not the same thing as an empty string or zero, although it is true that nulls being displayed as blanks by default are difficult to tell apart from empty strings.

I'm guessing that the OP's column is being populated with zeros because it has a default set.

 

[XelaIrodavlas]

They're set to zero because numeric fields cannot store a null value

Hi Frothingslosh. Lovely name btw. Can you please explain what you mean when you say they can't store a null value? Do you mean to say that it's it's bad practice or a physical impossibility? - because to my human eye it's never been an issue as long as the numeric field isn't a primary key and the 'Required' property is set to 'No'.

You can set the default to null in 2010 - has this feature been removed for 2013?

Sorry CJLondon I don't think I've explained myself very well: yes I can still set the default to null but I have to do it manually for every Numeric field. Not saying its a catastrophic failure but it is a confusing (and irritating) change.

I'm more convinced than ever that this just isn't possible... but hey. I've attached two images to demonstrate what i'm on about (I found a friend with Access 2007).

In both screenshots all I've done is create a table, made a field and given it 'number' for the data type. All field properties are as they first appear, seconds after adding the field, and you can see all the properties are identical except for the property 'default value': 2007 is null, 2013 is 0.

(So at least i was right in thinking this is new to Access 2013)

Maybe this problem sounds minor, but when you've got dozens of tables with dozens of numeric fields (only a tenth of which actually need 0 as the default) it quickly stacks up, and increases the chance of ballsing it up.

Thanks all,

 

[Frothingslosh]

Ah, crap, I'm thinking variables, not fields.

With fields, you can delete the value, but it's problematic to "insert" a 'null', as null specifically means 'no value stored', although there was a thread earlier covering that very topic and some ways to do it. That said, Access 2003 (which is what I use most of the time, as it's what we have at work) defaults number fields to having a default value of 0 - I just checked that while writing this post.

OP, what you might want to do is create some VBA to go through all the tables and change the numeric fields to not having a default value (and also to not have the field marked as 'required'). If you have exceptions, it wouldn't be that hard to code them into the procedure (or fix them by hand afterward if there are only a few).