473,569 Members | 2,542 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 "disconnect s" 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.
Nov 13 '05 #1
8 10397
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 "disconnect s" 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
Nov 13 '05 #2
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.o rg.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 "disconnect s" 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

Nov 13 '05 #3
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
Nov 13 '05 #4
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.o rg.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

Nov 13 '05 #5
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.o rg.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 "disconnect s" 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


Nov 13 '05 #6
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_djsteel e@NOSPAM_canada .com> wrote in message
news:fp******** ************@ro gers.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.o rg.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 "disconnect s" 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



Nov 13 '05 #7
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

Nov 13 '05 #8
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
Nov 13 '05 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1918
by: Norbert Nemec | last post by:
Hi there, is there any simple reason why IEEE special values are so poorly supported in python? Are there any serious efforts to change that? NaN and Inf are an extremely useful concept for numerical calculations and they would mix perfectly with the concepts in NumPy, anyhow, the support seems to be absolutely minimal. (The ideas in PEP...
5
45062
by: J. Muenchbourg | last post by:
IN the code below, I am getting an Incorrect Syntax near "," error (the sql execute line, and it is pointing to position 1 ) (the display formatting here may look different than my script): strSQLStatement = "INSERT INTO tblArticles(handid,articleid,articleDate,sport,articleheader,fpick,artic leText)"_ & "VALUES ('" & handid & "', " _ &...
0
2257
by: Dana | last post by:
I am using the XMLTextWriter to build an XML string and pass it to the XMLDocument. When I get the data from SQL Server, some of the values passed to the XML are NULL in the database. When I try and run an update to database using the same XML string, (using SQL parameters to pass the selectsinglenode), the XML always shows the NULL values...
4
25347
by: Guoqi Zheng | last post by:
On my application, I need to have different action based on the pass in query string. When the query string is not presented, I try to use If request.querystring("id") ="" THEN ...... This is what I did in trational asp, however if I did abov in ASP.NET, I always got an error of "Object reference not set to an instance of an object. " ...
10
3070
by: Larry Bird | last post by:
I'm trying to check for the presents of null values or null value not being present. My sample code below: If EmailAddresses Is Nothing Then SendMailMessage(EmailAddresses, AlertTitle, ViolationBody) End If
31
1783
by: Xero | last post by:
I have an array in my program. (Declared as letters(16, 16)). When I paused the program in debugging mode, I saw the following in the Autos window: letters |- (0, 0) | Nothing |- (0, 1) | Nothing
61
3112
by: academic | last post by:
When I declare a reference variable I initialize it to Nothing. Now I'm wondering if that best for String variables - is "" better? With Nothing I assume no memory is set aside nor GC'ed But with "" it is - correct? The system seems to handle a null the same as "".
1
2831
by: manchin2 | last post by:
Hi, Can anybody please provide the information about "&quot" and its use, if possible please provide an example. 1)<tm:bom-expression>{Conf.getEquityConfLookupFields().getEventFieldText(&quot;AdditionalDisruption&quot;,&quot;Change in Law&quot;)}</tm:bom-expression> 2)07:41:08 Default ( call ( . ( call ( . Conf getEquityConfLookupFields ) ) ...
4
3875
by: fran7 | last post by:
Hi, from help in the javascript forum I found the error in some code but need help. This bit of code works perfectly, trouble is I am writing it to a javascript function so the height needs to be in &quot;&quot; instead of "" otherwise I get an error message. Can anyone suggest how to write it so that it writes &quot; instead of "". I have tried all...
0
7700
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7614
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
1
7676
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7974
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6284
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5513
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3642
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2114
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
938
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.