By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,321 Members | 1,919 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,321 IT Pros & Developers. It's quick & easy.

Dates and nulls

JustJim
Expert 100+
P: 407
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

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.Exit_Date) Then
  2.     Orig_Exit_Date = Me.Exit_Date
  3. End If
  4.  
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
Sep 4 '07 #1
Share this Question
Share on Google+
4 Replies


ADezii
Expert 5K+
P: 8,669
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

Expand|Select|Wrap|Line Numbers
  1. If Not IsNull(Me.Exit_Date) Then
  2.     Orig_Exit_Date = Me.Exit_Date
  3. End If
  4.  
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim varDate As Variant
    2. varDate = Null
    3.  
    4. Debug.Print varDate     'produces Null
    5.  
    6. varDate = #3/17/1949#
    7.  
    8. Debug.Print IsDate(varDate)     'returns True
    9. Debug.Print VarType(varDate) = vbDate     'returns True
Sep 4 '07 #2

JustJim
Expert 100+
P: 407
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:
    Expand|Select|Wrap|Line Numbers
    1. Dim varDate As Variant
    2. varDate = Null
    3.  
    4. Debug.Print varDate     'produces Null
    5.  
    6. varDate = #3/17/1949#
    7.  
    8. Debug.Print IsDate(varDate)     'returns True
    9. Debug.Print VarType(varDate) = vbDate     'returns True
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
Sep 4 '07 #3

JustJim
Expert 100+
P: 407
Thanks ADezii, that worked fine and now my client is happy so therefore, so am I.

Jim
Sep 4 '07 #4

ADezii
Expert 5K+
P: 8,669
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:

Expand|Select|Wrap|Line Numbers
  1. Dim varDate As Variant or simply Dim varDate
  2. Debug.Print IsEmpty(varDate)       'returns True
Sep 4 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.