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

 

[Jamie Collins]

autonumbers are never zero.

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.

Also, you can adjust the seed value to auto-generate a zero value e.g.

CREATE TABLE Test11 (
ID IDENTITY(0,1) NOT NULL UNIQUE,
data_col VARCHAR(10) NOT NULL
)
;
INSERT INTO Test11 (data_col) VALUES ('Zero')
;
SELECT ID, data_col
FROM Test11
;

For convenience, here is the equivalent VBA demo:

Sub TestTim11()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection
.Execute _
"CREATE TABLE Test11 (ID IDENTITY(0,1) NOT" & _
" NULL UNIQUE, data_col VARCHAR(10) NOT NULL);"

.Execute _
"INSERT INTO Test11 (data_col) VALUES ('Zero');"

Dim rs As Object
Set rs = .Execute( _
"SELECT ID, data_col FROM Test11;")
MsgBox rs.GetString
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[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!

 

[Jamie Collins]

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.

Allen, I thought you didn't use the Numeric data type - you know you
can sort those negative values in a recordset, right <g>?

If this is a fair interpretation of the OP's point:

'I use a wizard to create my tables but it doesn't create them how I
want them.'

then this is a fair response:

'Don't use the wizard.'

Here's an alternative approach that uses code to create a table without
a default of zero:

Sub TestNumeric()
Kill "C:\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb;"

With .ActiveConnection
.Execute _
"CREATE TABLE TestNumeric (" & _
" key_col INTEGER NOT NULL UNIQUE," & _
" data_col NUMERIC(19, 5));"

.Execute _
"INSERT INTO TestNumeric (key_col)" & _
" VALUES (1);"

Dim rs As Object
Set rs = .Execute( _
"SELECT key_col, IIF(data_col IS NULL," & _
" '(NULL)', data_col) AS data_col" & _
" FROM TestNumeric;")
MsgBox rs.GetString
rs.Close
End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--

 

[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)

 

[Jamie Collins]

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.

Surely just about everyone knows how to insert a row and that numbers
can be zero? If you are going to make assumptions (e.g. in front end
application code) such as 'This column can never be zero' then all it
takes is a validation rule to enforce it and there's no problem.

Actually, you've mirrored a point I made upthread: if the Access
GUI/wizard/whatever doesn't do it how you want it then try another
approach. SQL DDL is the standard/portable way but I'll wager there's
some DAO code kicking about that achieves the same. So I agree: learn a
different approach, explicitly declare the default value (or lack
thereof) and the problem goes away.

Jamie.

--

 

[Jamie Collins]

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)

Going with the analogy, you seem to be saying, "I want to get to Upper
Hopton but the bus only goes as far as Mirfield." to which I'm
replying, "Don't take the bus. Find another way of getting to Upper
Hopton. I've made it to Upper Hopton. Here's the number of the local
taxi firm." Allen is saying, "I'm stuck in Mirfield, too. The bus
company have published plans to reroute the bus via Upper Hopton
sometime next year. Isn't that great news?"

JamieUK

--