Help with special values (Null, Empty, "", etc)

[Lyn]

I am trying to get my head around the concept of default, special or empty
values that appear in Access VBA, depending on data type. The Access Help
is not much (help), and the manual that I have is not much help here either.
Googling has given me a little help.

This is my current understanding -- I would appreciate any comments or
corrections...

"" -- this means an empty string when applied to String data type, and also
to Variant type when it is assumed to contain a string. It is also the
default value for String type.

0 -- this is the default value for any numeric data type.

00:00:00 -- this is the default (zero) value for any Date data type (if not
specifically formatted). I think that a "zero" date refers to the base date
and time of "1/1/100 00:00:00". I was using #1/1/100# as a flag to indicate
an unknown date, and I was getting an "unknown date" hit when the date value
was "empty".

Empty -- this is the default value for Variant data type. I presume that
this value has been defined because until the field has been initialized as
numeric or string, Access doesn't know whether to use 0 or "".

Null -- this applies to Variant data type and is there (I am guessing) as a
way of setting the value to "unknown" without specifying whether the field
should contain string or numeric data. It also applies to Boolean data type
when the normal value of True or False is not known (tri-state).

Nothing -- this applies to objects and is not really a value. Setting an
object name to Nothing simply "disconnects" the object name from any
resources. You cannot test (eg) "If RS = Nothing" as this does not make
sense. I am not sure how to test whether the object name is currently
associated with an object.

If I have missed out any important "special values", please feel free to
add. Also to comment on or correct the above. It will help me get a better
picture of how and when to use these values.

Thanks in advance.

--
Cheers,
Lyn.

[Trevor Best]

Lyn wrote:

I am trying to get my head around the concept of default, special or empty
values that appear in Access VBA, depending on data type. The Access Help
is not much (help), and the manual that I have is not much help here either.
Googling has given me a little help.

This is my current understanding -- I would appreciate any comments or
corrections...

"" -- this means an empty string when applied to String data type, and also
to Variant type when it is assumed to contain a string. It is also the
default value for String type.
Correct, in the case of a variant, you would have to purposely put ""
into it.
0 -- this is the default value for any numeric data type.
Correct
00:00:00 -- this is the default (zero) value for any Date data type (if not
specifically formatted). I think that a "zero" date refers to the base date
and time of "1/1/100 00:00:00". I was using #1/1/100# as a flag to indicate
an unknown date, and I was getting an "unknown date" hit when the date value
was "empty".
No, the base date (for zero) is 30 Dec 1899
Empty -- this is the default value for Variant data type. I presume that
this value has been defined because until the field has been initialized as
numeric or string, Access doesn't know whether to use 0 or "".
Yes, not only that but variants can be objects as well, hence not
initialised to null.
Null -- this applies to Variant data type and is there (I am guessing) as a
way of setting the value to "unknown" without specifying whether the field
should contain string or numeric data. It also applies to Boolean data type
when the normal value of True or False is not known (tri-state).
For a checkbox yes but a yes/no field in a Jet database cannot be null.

For nulls in general, the value is not present, it doesn't imply unknown
because you can put "unknown" in to a text field to say the value is
unknown, null simply means the data isn't present (see Codd's 3rd rule).
Some people do take it to mean the value is unknown though, particularly
for non text fields where you cannot enter "unknown".
Nothing -- this applies to objects and is not really a value. Setting an
object name to Nothing simply "disconnects" the object name from any
resources. You cannot test (eg) "If RS = Nothing" as this does not make
sense. I am not sure how to test whether the object name is currently
associated with an object.
Nothing and null are quite similiar in this respect, a variable can be
nothing, or be null but cannot =nothing or =null, to test is simple:

if objVariable is nothing then...

if Isnull(variable) then...
If I have missed out any important "special values", please feel free to
add. Also to comment on or correct the above. It will help me get a better
picture of how and when to use these values.

Thanks in advance.

All column data types except boolean (yes/no) can be and are initialised
to null in a table (unless a default value is specified).

The corresponding data types in VBA, (string, integer, long, date,
doubler, etc) cannot be null.

Variants can be null but are not initialised to null as they can be
objects too so are initialised as empty (test with isempty() function)

Object variables (dim x as object, and defined class objects, such as
recordset, form, querydef, etc) are initialised to nothing (test with if
variable is nothing then...)
--
This sig left intentionally blank

[Lyn]

Trevor,
Thank you for your response. This makes things a lot clearer.

I didn't realise that the default date was 30/12/1899. Which means that
earlier dates are negative. I suppose that this doubles the number of dates
available. I used to work with Progress DB where the zero date was
01/01/1900, so I shouldn't be surprised. However, working in a modern
business, I never had to deal with dates before about 1980!

Thanks again.
--
Cheers,
Lyn.

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...

Lyn wrote:

I am trying to get my head around the concept of default, special or
empty values that appear in Access VBA, depending on data type. The
Access Help is not much (help), and the manual that I have is not much
help here either. Googling has given me a little help.

This is my current understanding -- I would appreciate any comments or
corrections...

"" -- this means an empty string when applied to String data type, and
also to Variant type when it is assumed to contain a string. It is also
the default value for String type.

Correct, in the case of a variant, you would have to purposely put "" into
it.

0 -- this is the default value for any numeric data type.

Correct

00:00:00 -- this is the default (zero) value for any Date data type (if
not specifically formatted). I think that a "zero" date refers to the
base date and time of "1/1/100 00:00:00". I was using #1/1/100# as a
flag to indicate an unknown date, and I was getting an "unknown date" hit
when the date value was "empty".

No, the base date (for zero) is 30 Dec 1899

Empty -- this is the default value for Variant data type. I presume that
this value has been defined because until the field has been initialized
as numeric or string, Access doesn't know whether to use 0 or "".

Yes, not only that but variants can be objects as well, hence not
initialised to null.

Null -- this applies to Variant data type and is there (I am guessing) as
a way of setting the value to "unknown" without specifying whether the
field should contain string or numeric data. It also applies to Boolean
data type when the normal value of True or False is not known
(tri-state).

For a checkbox yes but a yes/no field in a Jet database cannot be null.

For nulls in general, the value is not present, it doesn't imply unknown
because you can put "unknown" in to a text field to say the value is
unknown, null simply means the data isn't present (see Codd's 3rd rule).
Some people do take it to mean the value is unknown though, particularly
for non text fields where you cannot enter "unknown".

Nothing -- this applies to objects and is not really a value. Setting an
object name to Nothing simply "disconnects" the object name from any
resources. You cannot test (eg) "If RS = Nothing" as this does not make
sense. I am not sure how to test whether the object name is currently
associated with an object.

Nothing and null are quite similiar in this respect, a variable can be
nothing, or be null but cannot =nothing or =null, to test is simple:

if objVariable is nothing then...

if Isnull(variable) then...

If I have missed out any important "special values", please feel free to
add. Also to comment on or correct the above. It will help me get a
better picture of how and when to use these values.

Thanks in advance.

All column data types except boolean (yes/no) can be and are initialised
to null in a table (unless a default value is specified).

The corresponding data types in VBA, (string, integer, long, date,
doubler, etc) cannot be null.

Variants can be null but are not initialised to null as they can be
objects too so are initialised as empty (test with isempty() function)

Object variables (dim x as object, and defined class objects, such as
recordset, form, querydef, etc) are initialised to nothing (test with if
variable is nothing then...)
--
This sig left intentionally blank

[Trevor Best]

Lyn wrote:

Trevor,
Thank you for your response. This makes things a lot clearer.

I didn't realise that the default date was 30/12/1899. Which means that
earlier dates are negative. I suppose that this doubles the number of dates
available. I used to work with Progress DB where the zero date was
01/01/1900, so I shouldn't be surprised. However, working in a modern
business, I never had to deal with dates before about 1980!

Not double, probably only back to about 1583 as the year before that was
pretty FUBAR by the missing days in October :-)

--
This sig left intentionally blank

[Lyn]

Good point. I think from memory the missing days occurred in different
countries at different times. It makes it all very confusing if you have to
use software like this for long ago dates. Still, I guess that few of us in
business have to go back that far! However, academics using Access to
create an historical database would be affected. But one would assume that
they would have written their own functions to deal with this.

--
Cheers,
Lyn.

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...

Lyn wrote:

Trevor,
Thank you for your response. This makes things a lot clearer.

I didn't realise that the default date was 30/12/1899. Which means that
earlier dates are negative. I suppose that this doubles the number of
dates available. I used to work with Progress DB where the zero date was
01/01/1900, so I shouldn't be surprised. However, working in a modern
business, I never had to deal with dates before about 1980!

Not double, probably only back to about 1583 as the year before that was
pretty FUBAR by the missing days in October :-)

--
This sig left intentionally blank

[Douglas J. Steele]

Dates are actually stored as 8 byte floating point numbers, where the
integer part represents the date as the number of days relative to 30 Dec,
1899, and the decimal part represents the time as a fraction of a day (6:00
AM is .25, Noon is .5, 6:00 PM is .75 and so on). They're good for dates
between 1 Jan, 100 and 31 Dec, 9999 (although, as Trevor's pointed out, it's
questionable of what value they are prior to the calendar reforms)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...

Trevor,
Thank you for your response. This makes things a lot clearer.

I didn't realise that the default date was 30/12/1899. Which means that
earlier dates are negative. I suppose that this doubles the number of
dates available. I used to work with Progress DB where the zero date was
01/01/1900, so I shouldn't be surprised. However, working in a modern
business, I never had to deal with dates before about 1980!

Thanks again.
--
Cheers,
Lyn.

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...

Lyn wrote:

I am trying to get my head around the concept of default, special or
empty values that appear in Access VBA, depending on data type. The
Access Help is not much (help), and the manual that I have is not much
help here either. Googling has given me a little help.

This is my current understanding -- I would appreciate any comments or
corrections...

"" -- this means an empty string when applied to String data type, and
also to Variant type when it is assumed to contain a string. It is also
the default value for String type.

Correct, in the case of a variant, you would have to purposely put ""
into it.

0 -- this is the default value for any numeric data type.

Correct

00:00:00 -- this is the default (zero) value for any Date data type (if
not specifically formatted). I think that a "zero" date refers to the
base date and time of "1/1/100 00:00:00". I was using #1/1/100# as a
flag to indicate an unknown date, and I was getting an "unknown date"
hit when the date value was "empty".

No, the base date (for zero) is 30 Dec 1899

Empty -- this is the default value for Variant data type. I presume
that this value has been defined because until the field has been
initialized as numeric or string, Access doesn't know whether to use 0
or "".

Yes, not only that but variants can be objects as well, hence not
initialised to null.

Null -- this applies to Variant data type and is there (I am guessing)
as a way of setting the value to "unknown" without specifying whether
the field should contain string or numeric data. It also applies to
Boolean data type when the normal value of True or False is not known
(tri-state).

For a checkbox yes but a yes/no field in a Jet database cannot be null.

For nulls in general, the value is not present, it doesn't imply unknown
because you can put "unknown" in to a text field to say the value is
unknown, null simply means the data isn't present (see Codd's 3rd rule).
Some people do take it to mean the value is unknown though, particularly
for non text fields where you cannot enter "unknown".

Nothing -- this applies to objects and is not really a value. Setting
an object name to Nothing simply "disconnects" the object name from any
resources. You cannot test (eg) "If RS = Nothing" as this does not make
sense. I am not sure how to test whether the object name is currently
associated with an object.

Nothing and null are quite similiar in this respect, a variable can be
nothing, or be null but cannot =nothing or =null, to test is simple:

if objVariable is nothing then...

if Isnull(variable) then...

If I have missed out any important "special values", please feel free to
add. Also to comment on or correct the above. It will help me get a
better picture of how and when to use these values.

Thanks in advance.

All column data types except boolean (yes/no) can be and are initialised
to null in a table (unless a default value is specified).

The corresponding data types in VBA, (string, integer, long, date,
doubler, etc) cannot be null.

Variants can be null but are not initialised to null as they can be
objects too so are initialised as empty (test with isempty() function)

Object variables (dim x as object, and defined class objects, such as
recordset, form, querydef, etc) are initialised to nothing (test with if
variable is nothing then...)
--
This sig left intentionally blank

[Lyn]

I was aware of the date vs time format. But since you raise it, your
comment that .75 is 6:00 pm and is good between dates 1/1/100 and
31/12/9999 -- wouldn't .75 in dates before 1900 (ie, negative FP values) be
6:00 *AM* ??? Or does time always progress throughout the day from .0
through .9999 in the positive direction, regardless of whether the date
portion is negative?

--
Cheers,
Lyn.

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:fp********************@rogers.com...

Dates are actually stored as 8 byte floating point numbers, where the
integer part represents the date as the number of days relative to 30 Dec,
1899, and the decimal part represents the time as a fraction of a day
(6:00 AM is .25, Noon is .5, 6:00 PM is .75 and so on). They're good for
dates between 1 Jan, 100 and 31 Dec, 9999 (although, as Trevor's pointed
out, it's questionable of what value they are prior to the calendar
reforms)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Lyn" <lh******@ihug.com.au> wrote in message
news:cr**********@lust.ihug.co.nz...

Trevor,
Thank you for your response. This makes things a lot clearer.

I didn't realise that the default date was 30/12/1899. Which means that
earlier dates are negative. I suppose that this doubles the number of
dates available. I used to work with Progress DB where the zero date was
01/01/1900, so I shouldn't be surprised. However, working in a modern
business, I never had to deal with dates before about 1980!

Thanks again.
--
Cheers,
Lyn.

"Trevor Best" <no****@besty.org.uk> wrote in message
news:41***********************@news.zen.co.uk...

Lyn wrote:
I am trying to get my head around the concept of default, special or
empty values that appear in Access VBA, depending on data type. The
Access Help is not much (help), and the manual that I have is not much
help here either. Googling has given me a little help.

This is my current understanding -- I would appreciate any comments or
corrections...

"" -- this means an empty string when applied to String data type, and
also to Variant type when it is assumed to contain a string. It is
also the default value for String type.

Correct, in the case of a variant, you would have to purposely put ""
into it.

0 -- this is the default value for any numeric data type.

Correct

00:00:00 -- this is the default (zero) value for any Date data type (if
not specifically formatted). I think that a "zero" date refers to the
base date and time of "1/1/100 00:00:00". I was using #1/1/100# as a
flag to indicate an unknown date, and I was getting an "unknown date"
hit when the date value was "empty".

No, the base date (for zero) is 30 Dec 1899

Empty -- this is the default value for Variant data type. I presume
that this value has been defined because until the field has been
initialized as numeric or string, Access doesn't know whether to use 0
or "".

Yes, not only that but variants can be objects as well, hence not
initialised to null.

Null -- this applies to Variant data type and is there (I am guessing)
as a way of setting the value to "unknown" without specifying whether
the field should contain string or numeric data. It also applies to
Boolean data type when the normal value of True or False is not known
(tri-state).

For a checkbox yes but a yes/no field in a Jet database cannot be null.

For nulls in general, the value is not present, it doesn't imply unknown
because you can put "unknown" in to a text field to say the value is
unknown, null simply means the data isn't present (see Codd's 3rd rule).
Some people do take it to mean the value is unknown though, particularly
for non text fields where you cannot enter "unknown".

Nothing -- this applies to objects and is not really a value. Setting
an object name to Nothing simply "disconnects" the object name from any
resources. You cannot test (eg) "If RS = Nothing" as this does not
make sense. I am not sure how to test whether the object name is
currently associated with an object.

Nothing and null are quite similiar in this respect, a variable can be
nothing, or be null but cannot =nothing or =null, to test is simple:

if objVariable is nothing then...

if Isnull(variable) then...

If I have missed out any important "special values", please feel free
to add. Also to comment on or correct the above. It will help me get
a better picture of how and when to use these values.

Thanks in advance.

All column data types except boolean (yes/no) can be and are initialised
to null in a table (unless a default value is specified).

The corresponding data types in VBA, (string, integer, long, date,
doubler, etc) cannot be null.

Variants can be null but are not initialised to null as they can be
objects too so are initialised as empty (test with isempty() function)

Object variables (dim x as object, and defined class objects, such as
recordset, form, querydef, etc) are initialised to nothing (test with if
variable is nothing then...)
--
This sig left intentionally blank

[Rick Brandt]

Lyn wrote:

I was aware of the date vs time format. But since you raise it, your
comment that .75 is 6:00 pm and is good between dates 1/1/100 and
31/12/9999 -- wouldn't .75 in dates before 1900 (ie, negative FP
values) be 6:00 *AM* ??? Or does time always progress throughout the
day from .0 through .9999 in the positive direction, regardless of
whether the date portion is negative?

The latter which is why performing straight math with DateValues doesn't always
work whereas the built in Date functions handle this properly.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Trevor Best]

Lyn wrote:

Good point. I think from memory the missing days occurred in different
countries at different times. It makes it all very confusing if you have to
use software like this for long ago dates. Still, I guess that few of us in
business have to go back that far! However, academics using Access to
create an historical database would be affected. But one would assume that
they would have written their own functions to deal with this.

I should hope so, things like SQL Server only go back to 1900 I think
(or was it 30/31 Dec 1899?). The Catholics and protestants changed their
calendars at different points so it could be a different date depending
on the village you were in, and we worried about Y2K? <g>

--
This sig left intentionally blank