Null's and dates

[Kris M]

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

[Kris M]

Re: Null's and dates

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" <tiredofit@nosp am.com> wrote in message
news:hQmNa.1441 00$Dr3.28693@fe d1read02...[color=blue]
> "Kris M" <maccxoph@yahoo .com> wrote in message
> news:HoecnRADLc sqS5iiXTWJkQ@co mcast.com[color=green]
> > 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?[/color]
>
> 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[/color]
Null;[color=blue]
> 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
>[/color]

[Bert Byfield]

Re: Null's and dates

>> How do i handle a null value for a date variable type. I am[color=blue][color=green]
>> 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?[/color][/color]
[color=blue]
>You have 3 choices that I know of:
>1) use Variant instead of Date in the array[/color]

This works, but is complicated.
[color=blue]
>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[/color]

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

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

[Kris M]

Re: Null's and dates

can empty be used in numeric data types as in null entry in DB field with
long value = empty in array?
"Bert Byfield" <BertByfield@no spam.not> wrote in message
news:93AEED128b ertbyfieldcarav elab@24.24.2.16 6...[color=blue][color=green][color=darkred]
> >> 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?[/color][/color]
>[color=green]
> >You have 3 choices that I know of:
> >1) use Variant instead of Date in the array[/color]
>
> This works, but is complicated.
>[color=green]
> >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[/color]
>
> This works, but trashes reports or forces you to blank out your special
> dates somehow.
>[color=green]
> >3) keep an array of flags indicating valid/invalid for each date and
> >substitute Null for any flagged as invalid when updating the database[/color]
>
> 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 < & "" >.
>
>
>
>
>
>
>
>[/color]

[Bob Butler]

Re: Null's and dates

"Bert Byfield" <BertByfield@no spam.not> wrote in message
news:93AEED128b ertbyfieldcarav elab@24.24.2.16 6
<cut>[color=blue]
> Better: You can put "Empty" into a date field when the original data
> is null.[/color]

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.

[Geoff]

Re: Null's and dates

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).Va lue = Null
DTPicker1(0).ZO rder 0
Else
DTPicker1(1).Va lue = myDate
DTPicker1(1).ZO rder 0
End If
End Sub
Private Sub DTPicker1_Chang e(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).Ch eckBox = True
DTPicker1(1).Ch eckBox = False
myDate = Null
SetValues
End Sub
Regards
Geoff

"Kris M" <maccxoph@yahoo .com> wrote in message news:<CTidnXoQw sh1h5uiXTWJhg@c omcast.com>...[color=blue]
> 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" <tiredofit@nosp am.com> wrote in message
> news:hQmNa.1441 00$Dr3.28693@fe d1read02...[color=green]
> > "Kris M" <maccxoph@yahoo .com> wrote in message
> > news:HoecnRADLc sqS5iiXTWJkQ@co mcast.com[color=darkred]
> > > 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?[/color]
> >
> > 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[/color]
> Null;[color=green]
> > 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
> >[/color][/color]

[Bert Byfield]

Re: Null's and dates

>can empty be used in numeric data types as in null entry in DB field with[color=blue]
>long value = empty in array?[/color]

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