Test Date data type when un-initialized

[Bill]

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Allen Browne]

A Date field that contains no value is Null, so the correct test in code is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the field's
Allow Zero Length property to Yes (opposite of previous versions), so you
now have to explicitly set the property to No *every* time you add a Text
field to a table.

The terms Empty, Nothing, and Missing to not apply to field values. Empty is
the value of an uninitialized Variant in VBA code. Nothing is the value of
an uninitialized object in VBA code. Missing is the value of an optional
paramter that was not supplied in VBA code.

 

[Bill]

Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in code
is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous versions),
so you now have to explicitly set the property to No *every* time you add
a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values. Empty
is the value of an uninitialized Variant in VBA code. Nothing is the value
of an uninitialized object in VBA code. Missing is the value of an
optional paramter that was not supplied in VBA code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Douglas J. Steele]

A table field can be set to Null, regardless of its data type, as long as
its Required property isn't set to True.

The only variable type that can be set to Null, though, is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in code
is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous
versions), so you now have to explicitly set the property to No *every*
time you add a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values. Empty
is the value of an uninitialized Variant in VBA code. Nothing is the
value of an uninitialized object in VBA code. Missing is the value of an
optional paramter that was not supplied in VBA code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Bill]

Thanks Doug,
I just spent some time at Allen's http://allenbrowne.com/tips.html
website and got some of my long-standing confusion cleared up,
though your lesson hear about the use of Null on fields requiring
values helps even further.

I had a thread going yesterday trying to find out how to initialize
a new record wherein one of the fields would initially be set to null.
I.e., Insert(............, MyDateField) Values(..............., Null) but
SQL went nuts when I tried specifiying the value as null. Would it
work if I instead coded:

Dim NullDate as Variant
NullDate = Null
Insert(............, MyDateField) Values(..............., NullDate)


Thanks again,
Bill

A table field can be set to Null, regardless of its data type, as long as
its Required property isn't set to True.

The only variable type that can be set to Null, though, is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in code
is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous
versions), so you now have to explicitly set the property to No *every*
time you add a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values.
Empty is the value of an uninitialized Variant in VBA code. Nothing is
the value of an uninitialized object in VBA code. Missing is the value
of an optional paramter that was not supplied in VBA code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Allen Browne]

Bill, if you have a field in your table, and you enter a record without
putting anything in that field, the blank field is Null.

If you enter a value, and then remove it, the value is now Null.

You can also set it to null programmatically: exactly the same effect as
deleting the data out of the field.

As Douglas pointed out you cannot delete the value from the field if its
Required property is Yes. Required is the oppositie of Nullable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Doug,
I just spent some time at Allen's http://allenbrowne.com/tips.html
website and got some of my long-standing confusion cleared up,
though your lesson hear about the use of Null on fields requiring
values helps even further.

I had a thread going yesterday trying to find out how to initialize
a new record wherein one of the fields would initially be set to null.
I.e., Insert(............, MyDateField) Values(..............., Null) but
SQL went nuts when I tried specifiying the value as null. Would it
work if I instead coded:

Dim NullDate as Variant
NullDate = Null
Insert(............, MyDateField) Values(..............., NullDate)


Thanks again,
Bill

A table field can be set to Null, regardless of its data type, as long as
its Required property isn't set to True.

The only variable type that can be set to Null, though, is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in code
is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous
versions), so you now have to explicitly set the property to No *every*
time you add a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values.
Empty is the value of an uninitialized Variant in VBA code. Nothing is
the value of an uninitialized object in VBA code. Missing is the value
of an optional paramter that was not supplied in VBA code.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Bill]

Nope!

Thanks Doug,
I just spent some time at Allen's http://allenbrowne.com/tips.html
website and got some of my long-standing confusion cleared up,
though your lesson hear about the use of Null on fields requiring
values helps even further.

I had a thread going yesterday trying to find out how to initialize
a new record wherein one of the fields would initially be set to null.
I.e., Insert(............, MyDateField) Values(..............., Null) but
SQL went nuts when I tried specifiying the value as null. Would it
work if I instead coded:

Dim NullDate as Variant
NullDate = Null
Insert(............, MyDateField) Values(..............., NullDate)


Thanks again,
Bill

A table field can be set to Null, regardless of its data type, as long as
its Required property isn't set to True.

The only variable type that can be set to Null, though, is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in code
is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous
versions), so you now have to explicitly set the property to No *every*
time you add a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values.
Empty is the value of an uninitialized Variant in VBA code. Nothing is
the value of an uninitialized object in VBA code. Missing is the value
of an optional paramter that was not supplied in VBA code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.

 

[Bill]

Got it Allen, thanks for helping to finally clear up the
whole concept for me.
Bill
(PS) I've bookmarked your site

Bill, if you have a field in your table, and you enter a record without
putting anything in that field, the blank field is Null.

If you enter a value, and then remove it, the value is now Null.

You can also set it to null programmatically: exactly the same effect as
deleting the data out of the field.

As Douglas pointed out you cannot delete the value from the field if its
Required property is Yes. Required is the oppositie of Nullable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Doug,
I just spent some time at Allen's http://allenbrowne.com/tips.html
website and got some of my long-standing confusion cleared up,
though your lesson hear about the use of Null on fields requiring
values helps even further.

I had a thread going yesterday trying to find out how to initialize
a new record wherein one of the fields would initially be set to null.
I.e., Insert(............, MyDateField) Values(..............., Null) but
SQL went nuts when I tried specifiying the value as null. Would it
work if I instead coded:

Dim NullDate as Variant
NullDate = Null
Insert(............, MyDateField) Values(..............., NullDate)


Thanks again,
Bill

A table field can be set to Null, regardless of its data type, as long as
its Required property isn't set to True.

The only variable type that can be set to Null, though, is the Variant.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Can I infer from what you've said that table fields can
explicitly be set to null? E.g., Me.MyDate = Null

If I Dim a VBA variable as Date, I know I can't set
that type of variable to Null without encountering a
runtime error.

I tried to ferret out these answers from HELP, but
there seems to be a lacking of such discussions.

Thanks,
Bill

A Date field that contains no value is Null, so the correct test in
code is:
IsNull([MyDateField])
or in the context of an SQL statement:
WHERE [MyDateField] Is Null

Any field with no value is Null.

Text fields (including Memo and Hyperlink) can also contain a
Zero-Length-String, but in 99.99% of cases this is highly undesirable.
Unfortunately, Access 2000 and later suddenly decided to default the
field's Allow Zero Length property to Yes (opposite of previous
versions), so you now have to explicitly set the property to No
*every* time you add a Text field to a table.

The terms Empty, Nothing, and Missing to not apply to field values.
Empty is the value of an uninitialized Variant in VBA code. Nothing is
the value of an uninitialized object in VBA code. Missing is the value
of an optional paramter that was not supplied in VBA code.

If a table field defined as Date is un-initialized,
what is it, Empty, Null or zero length? Though
they are normally stored as 64 bit numbers, I
see that un-initialized fields are not displayed as
zero. Thus, it is not clear the proper way to test
the field to determine if it is un-initialized.