Dates and nulls

[JustJim]

I am aware that a date datatype field in a table can have the default value of 0 removed so that the field effectively is null until changed. This is usefull for displaying table contents on a form.

I want to manipulate dates programatically but when I Dim a variable AS Date, the variable (which I know is really a long integer) contains 0 and as a date is 12:00:00 of some long forgotten (even by me) date in the past!

I need to do something like this

[CODE=vb]If Not IsNull(Me.Exit_ Date) Then
Orig_Exit_Date = Me.Exit_Date
End If
[/CODE]

Now I can check to see if the date on the form (Me.Exit_Date) is null and if so, ignore it, but the variable (Orig_Exit_Date ) is already 0 so if I don't assign it a real date because the one on the form is null, I get ancient history.

How do I trick the variable (Orig_Exit_Date ) into thinking it is empty?

Thanks

Jim

[ADezii]

I am aware that a date datatype field in a table can have the default value of 0 removed so that the field effectively is null until changed. This is usefull for displaying table contents on a form.

I want to manipulate dates programatically but when I Dim a variable AS Date, the variable (which I know is really a long integer) contains 0 and as a date is 12:00:00 of some long forgotten (even by me) date in the past!

I need to do something like this

[CODE=vb]If Not IsNull(Me.Exit_ Date) Then
Orig_Exit_Date = Me.Exit_Date
End If
[/CODE]

Now I can check to see if the date on the form (Me.Exit_Date) is null and if so, ignore it, but the variable (Orig_Exit_Date ) is already 0 so if I don't assign it a real date because the one on the form is null, I get ancient history.

How do I trick the variable (Orig_Exit_Date ) into thinking it is empty?

Thanks

Jim

If you are that concerned about an uninitialized Date Variable containing some obscure value:

1. Declare the Variable representing the Date as Variant.
2. Assign it the value of Null.
3. VBA will coerce it to the appropriate Date Data Type when appropriate.
4. A simple code segment will illustrate this point:
   [CODE=vb]Dim varDate As Variant
   varDate = Null
   
   Debug.Print varDate 'produces Null
   
   varDate = #3/17/1949#
   
   Debug.Print IsDate(varDate) 'returns True
   Debug.Print VarType(varDate ) = vbDate 'returns True[/CODE]

[JustJim]

If you are that concerned about an uninitialized Date Variable containing some obscure value:

1. Declare the Variable representing the Date as Variant.
2. Assign it the value of Null.
3. VBA will coerce it to the appropriate Date Data Type when appropriate.
4. A simple code segment will illustrate this point:
   [CODE=vb]Dim varDate As Variant
   varDate = Null
   
   Debug.Print varDate 'produces Null
   
   varDate = #3/17/1949#
   
   Debug.Print IsDate(varDate) 'returns True
   Debug.Print VarType(varDate ) = vbDate 'returns True[/CODE]

I'm not that fussed, customer is... Just thinking though, if I declare a date-ish variable as Variant, do I need to assign it to Null? Empty would achieve the same thing yesno?

Jim

[JustJim]

Thanks ADezii, that worked fine and now my client is happy so therefore, so am I.

Jim

[ADezii]

Thanks ADezii, that worked fine and now my client is happy so therefore, so am I.

Jim

You are quite welcome. JustJim.

Just thinking though, if I declare a date-ish variable as Variant, do I need to assign it to Null?

No, you do not need to assign it the value NULL, for some reason I assumed you wanted the Variable to return NULL if it wasn't initialized. The Variable will simply be Empty, which simply means that it was never iniitialized as in:

[CODE=vb]Dim varDate As Variant or simply Dim varDate
Debug.Print IsEmpty(varDate ) 'returns True[/CODE]