469,326 Members | 1,478 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Null's and dates

How do i handle a null value for a date variable type. I am retrieving date
data from an access database and storing the records in an array for
processing. The array field has a date type and the db records are date type
with null permissible.
How should i handle the insertion of a null date record into the array? I
just cant leave it empty right?
K
Jul 17 '05 #1
6 34539
I wanted to avoid the size penalty when using variants but that would have
been easier. I decided to write a defauct date constant wiuth the value of
1-1-1900 and use that. I will just have to translate that when i use it in a
DTP control. Anyone know of a 3rd party control to replace the DTP control
that can display null date values?
I dont think the array is useful to me in this project but i like the idea..
Thanks
"Bob Butler" <ti*******@nospam.com> wrote in message
news:hQmNa.144100$Dr3.28693@fed1read02...
"Kris M" <ma******@yahoo.com> wrote in message
news:Ho********************@comcast.com
How do i handle a null value for a date variable type. I am
retrieving date data from an access database and storing the records
in an array for processing. The array field has a date type and the
db records are date type with null permissible.
How should i handle the insertion of a null date record into the
array? I just cant leave it empty right?
You have 3 choices that I know of:
1) use Variant instead of Date in the array
2) pick some "invalid" date and use that in the array to stand in for

Null; for example, if valid dates are all recent you could use 1/1/1900 to mean
"null" when updating the database
3) keep an array of flags indicating valid/invalid for each date and
substitute Null for any flagged as invalid when updating the database

Jul 17 '05 #2
>> How do i handle a null value for a date variable type. I am
retrieving date data from an access database and storing the records
in an array for processing. The array field has a date type and the
db records are date type with null permissible.
How should i handle the insertion of a null date record into the
array? I just cant leave it empty right?
You have 3 choices that I know of:
1) use Variant instead of Date in the array
This works, but is complicated.
2) pick some "invalid" date and use that in the array to stand in for
Null; for example, if valid dates are all recent you could use 1/1/1900
to mean "null" when updating the database
This works, but trashes reports or forces you to blank out your special
dates somehow.
3) keep an array of flags indicating valid/invalid for each date and
substitute Null for any flagged as invalid when updating the database


Works, but way too complicated.

Better: You can put "Empty" into a date field when the original data is
null. Then reports will correctly show "no date" for Empty dates. Just
juggle the code to convert null to empty when reading from the database,
watching out for the deadly null runtime error by using < & "" >.


Jul 17 '05 #3
can empty be used in numeric data types as in null entry in DB field with
long value = empty in array?
"Bert Byfield" <Be*********@nospam.not> wrote in message
news:93***************************@24.24.2.166...
How do i handle a null value for a date variable type. I am
retrieving date data from an access database and storing the records
in an array for processing. The array field has a date type and the
db records are date type with null permissible.
How should i handle the insertion of a null date record into the
array? I just cant leave it empty right?

You have 3 choices that I know of:
1) use Variant instead of Date in the array


This works, but is complicated.
2) pick some "invalid" date and use that in the array to stand in for
Null; for example, if valid dates are all recent you could use 1/1/1900
to mean "null" when updating the database


This works, but trashes reports or forces you to blank out your special
dates somehow.
3) keep an array of flags indicating valid/invalid for each date and
substitute Null for any flagged as invalid when updating the database


Works, but way too complicated.

Better: You can put "Empty" into a date field when the original data is
null. Then reports will correctly show "no date" for Empty dates. Just
juggle the code to convert null to empty when reading from the database,
watching out for the deadly null runtime error by using < & "" >.



Jul 17 '05 #4
"Bert Byfield" <Be*********@nospam.not> wrote in message
news:93***************************@24.24.2.166
<cut>
Better: You can put "Empty" into a date field when the original data
is null.


VB Date values can not be 'Empty', only Variants can hold that setting.
When you assign Empty to a Date in VB it simply sets the underlying numeric
value to zero which is midnight at the start of Dec 30, 1899

Dim d As Date
d = Empty
Debug.Print Format$(d, "yyyy-mm-dd hh:nn:ss")

1899-12-30 00:00:00

Using Empty as you suggest is no different than picking any other date to
represent 'no date' and, IMO, makes the code more confusing because it
implies something that isn't happening.

Jul 17 '05 #5
I *always* use UDTs to store the data from the DB source dll , exe
and try to avoid assigning dummy values if poss I think its better if they
represent what thay are at all times.
If I do not re-assign null date values once set
I use an array of 2 DTP's for each date
So it displays an empty checkbox to indicate a null value.
Once a date is set I never see the check box again.
If you can stand that initial check box against a blank box
then you can use a 0-1 DTP array with nulls like so:-

Option Explicit
Dim myDate
Sub SetValues()
If IsNull(myDate) Then
DTPicker1(0).Value = Null
DTPicker1(0).ZOrder 0
Else
DTPicker1(1).Value = myDate
DTPicker1(1).ZOrder 0
End If
End Sub
Private Sub DTPicker1_Change(Index As Integer)
'User Change Values
'Used to remove checkbox on first use
'If Not Index Then DTPicker1(Index).ZOrder 0
myDate = DTPicker1(Index).Value
End Sub
Private Sub Form_Load()
'Set defaults maybe at design time
DTPicker1(0).CheckBox = True
DTPicker1(1).CheckBox = False
myDate = Null
SetValues
End Sub
Regards
Geoff

"Kris M" <ma******@yahoo.com> wrote in message news:<CT********************@comcast.com>...
I wanted to avoid the size penalty when using variants but that would have
been easier. I decided to write a defauct date constant wiuth the value of
1-1-1900 and use that. I will just have to translate that when i use it in a
DTP control. Anyone know of a 3rd party control to replace the DTP control
that can display null date values?
I dont think the array is useful to me in this project but i like the idea..
Thanks
"Bob Butler" <ti*******@nospam.com> wrote in message
news:hQmNa.144100$Dr3.28693@fed1read02...
"Kris M" <ma******@yahoo.com> wrote in message
news:Ho********************@comcast.com
How do i handle a null value for a date variable type. I am
retrieving date data from an access database and storing the records
in an array for processing. The array field has a date type and the
db records are date type with null permissible.
How should i handle the insertion of a null date record into the
array? I just cant leave it empty right?


You have 3 choices that I know of:
1) use Variant instead of Date in the array
2) pick some "invalid" date and use that in the array to stand in for

Null;
for example, if valid dates are all recent you could use 1/1/1900 to mean
"null" when updating the database
3) keep an array of flags indicating valid/invalid for each date and
substitute Null for any flagged as invalid when updating the database

Jul 17 '05 #6
>can empty be used in numeric data types as in null entry in DB field with
long value = empty in array?


What you do to find out stuff like this (which I did for dates) is to open
VB6 and start a temporary project and just double-click on the form and
enter a form load event code of a long or date or whatever and
"debug.assert false" and run it, and in the immediate window try to assign
"n = empty". If you get an error, it isn't legal. If it seems okay, enter
"? n" in the immediate window and see what displays. You need to learn to
put these questions to a trial and error test, because asking questions
takes too long. The trial and error procedure only takes a few minutes, and
gives you verified results.


Jul 17 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Boefje | last post: by
8 posts views Thread by craigkenisston | last post: by
4 posts views Thread by wapsiii | last post: by
4 posts views Thread by tlyczko | last post: by
4 posts views Thread by Jeff Goodman | last post: by
2 posts views Thread by paulquinlan100 | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.