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.

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

Correct

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

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

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 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...

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,
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" <nospam (AT) besty (DOT) org.uk> wrote

Lyn wrote:
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

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" <nospam (AT) besty (DOT) org.uk> wrote

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" <lhancock (AT) ihug (DOT) com.au> 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?

--
Cheers,
Lyn.

"Douglas J. Steele" <NOSPAM_djsteele (AT) NOSPAM_canada (DOT) com> wrote

Lyn wrote:
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