Basic Questions about Null values in tables

[Dave]

Access 2002 - I have several tables which contain null values. The fields
are text, numbers, memos and dates.

I am using Access as a server side db.

Is it a bad idea to have null values in a table?

If I do not want a null value, then how do I say I do not want a null as a
default?

If I do not want a null, is more space occupied for the table\database (does
the mdb file get larger)?

Is there an auto size feature for a record field? (e.g. I may set a text
field at 6 characters.)

Thanks in advance

Dave

 

[Alex White MCDBA MCSE]

I'll answer inline...

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

Access 2002 - I have several tables which contain null values. The fields
are text, numbers, memos and dates.

I am using Access as a server side db.

Is it a bad idea to have null values in a table?

your coding and verification becomes easer as you don't have to test for
null values,.

If I do not want a null value, then how do I say I do not want a null as a
default?

either within the table design or on the forms that edit the data you can
set defaults.

If I do not want a null, is more space occupied for the table\database
(does the mdb file get larger)?

well null is nothing, but we are talking small potato's here, access db's
can be 2gb in size.

Is there an auto size feature for a record field? (e.g. I may set a text
field at 6 characters.)

when designing a table you specify how large you want the field. Memo fields
don't have a specific size they are as large as you want within reason.

 

[Dave]

Thanks

So in a Table text fiedl, if I do not want to have a null, do I enter a ""
and for a number 0

Thanks

 

[Alex White MCDBA MCSE]

Hi Dave,

the numeric is easy = 0

strings well yes you could do "", but not allowing zero length strings will
help.

date fields = now (if you want to enter todays date)

yes/no field (true/false) you should set these defaults one way or the
other.

the trick here is get your database to do as much of the work and
enforcement of data rules, streamline the front-end by making the back-end
enfore the rules.

see the problem with null's is this

take this bit of code

dim intTest as integer
intTest = 0
if not isnull(.fields("test").value) then
if isnumeric(.fields("test").value) then
intTest = val(.fields("test").value)
end if
end if

or

dim intTest as integer
intTest = 0
if isnumeric(.fields("test").value) then
intTest = val(.fields("test").value)
end if


of course the above code assumes that the field is a text field, you can see
how you have to evaluate isnull all the time,

you can also run functions into default values to help fill the tables for
you.

 

[Dave]

Thanks!

This is a start.

Hi Dave,

the numeric is easy = 0

strings well yes you could do "", but not allowing zero length strings
will help.

date fields = now (if you want to enter todays date)

yes/no field (true/false) you should set these defaults one way or the
other.

the trick here is get your database to do as much of the work and
enforcement of data rules, streamline the front-end by making the back-end
enfore the rules.

see the problem with null's is this

take this bit of code

dim intTest as integer
intTest = 0
if not isnull(.fields("test").value) then
if isnumeric(.fields("test").value) then
intTest = val(.fields("test").value)
end if
end if

or

dim intTest as integer
intTest = 0
if isnumeric(.fields("test").value) then
intTest = val(.fields("test").value)
end if


of course the above code assumes that the field is a text field, you can
see how you have to evaluate isnull all the time,

you can also run functions into default values to help fill the tables for
you.

--
Regards

Alex White MCDBA MCSE
http://www.intralan.co.uk

 

[Douglas J. Steele]

your coding and verification becomes easer as you don't have to test for
null values,.

To me, ease of programming isn't a legimate reason not to allow nulls. The
only question that's relevant is can you live with (sometime misleading)
default values. This is especially true of numeric fields: having a 0 when
you don't in fact know the value can cause problems.