Default to Null

[JethroUK©]

numeric fields seem to default to zero - despite having no apparent default
value - you would imagine that if a field defaults to zero, then zero would
appear in it's default value - hence it seems a little counter-intuative

if i need a numeric field to default to null - is it proper practice to set:

default: =Null

or have i missed something?

 

[Tim Ferguson]

numeric fields seem to default to zero - despite having no apparent
default value - you would imagine that if a field defaults to zero,
then zero would appear in it's default value - hence it seems a little
counter-intuative

I am not sure what you are complaining about here. One of Access's huge
annoyances is that all numeric fields have their DefaultValue set to Zero
by default. This behaviour is designed to break databases where foreign
keys are set to point at Autonumber PKs, since autonumbers are never
zero.

Once you have noticed this it becomes a chore to reset all the default
values back to blank (or "Null") in the table design window whenever you
are creating a table.

I have, however, never seen a default value set to anything while the
DefaultValue in the table design window is empty. Are you sure it's the
field-level default value that is creating the values, and not a control
on the form or report?

Best wishes


Tim F

 

[JethroUK©]

I am not sure what you are complaining about here.

not complaining as much as wondering - whilst it may have obvious benefits
for numeric fields to default to zero - it would in turn make sense to
include this in the default value property - so that the designer (in this
moi), can understand what's happening

so back to original question - i cases where i actually need null default -
is it good practise for me to set the default value: = Null ?

to be honest i'm not quite sure where the zero default is comming from,
because the table is via a form & is also used as a lookup to another
table - all i know for sure is that it has to default to null

 

[Allen Browne]

It looks like you are not the only person who found the 0 default annoying.
Numeric fields default to Null as they should in A2007.

This is great No more 0 default messing up foreign keys!

 

[Tim Ferguson]

Not true.

You set an explicit autonumber (a.k.a. IDENTITY) value using the INSERT
statement and there is nothing to prevent the value from being zero.

(neatly sidestepping the whole semantic thing about whether a Jet INTEGER
IDENTITY(x, y) is the same as an Access Autonumber...)

Anyone who knows how to set up the DDL to create a table with an identity
column is not going to post here wondering about default values.
Sometimes you just have to simplify.

B Wishes


Tim F

 

[Tim Ferguson]

it would in turn make sense to
include this in the default value property - so that the designer (in
this moi), can understand what's happening

This is the bit I don't understand. You seem to be claiming that there is
no entry in the DefaultValue box in the Table Designer for the field
under discussion. I have never known this. If this is a default
DefaultValue then it's there in the Table Designer. If you can see an
empty box, please post the exact steps you took so that someone else can
repeat this. It sounds very odd even if, as Allen says, it's shortly
going to be of historical interest only.

so back to original question - i cases where i actually need null
default - is it good practise for me to set the default value: = Null

I would say "neccessary" rather than "good practice" but anyway... Yes.


Tim F

 

[JethroUK©]

This is the bit I don't understand. You seem to be claiming that there is
no entry in the DefaultValue box in the Table Designer for the field
under discussion. I have never known this.

to be honest - that's the bit that puzzled me (as counter-intuative) - like
i say, the table runs via a form (i created) that's coded beyond my memory
span & it's not beyond possibilty i have set it in code somewhere and i'm
blaming the table for my own actions - in view of your comment i suspect
this is what has happened & i'll have to trawl through the code and see if i
can find it

 

[JethroUK©]

(neatly sidestepping the whole semantic thing about whether a Jet INTEGER
IDENTITY(x, y) is the same as an Access Autonumber...)

Anyone who knows how to set up the DDL to create a table with an identity
column is not going to post here wondering about default values.
Sometimes you just have to simplify.

i'll buy that - often the poster (this case me) hasn't got a clue what
they're doing/talking about, to the extent that they aren't even asking the
right question ('how can i fit a new battery on my car?') - which is the
experts cue to rise above it and say "weeeeell i could show you, but that
wont start your car! - because it's the alternator that's buggered (the real
answer)