Clearing out Date Variables

[Pam Poulos]

I have a form where users input some dates, these dates are then written to date variables.

I need to clear out the date variables with either an empty string or a null value. Currently, I am using 12:00 AM to clear out the date value, however this is now causing confusion.

Can anyone tell me out to clear out the variable so it is empty.

Thanks so much

Pam

[ADezii]

I have a form where users input some dates, these dates are then written to date variables.

I need to clear out the date variables with either an empty string or a null value. Currently, I am using 12:00 AM to clear out the date value, however this is now causing confusion.

Can anyone tell me out to clear out the variable so it is empty.

Thanks so much

Pam

What you can't do:
__1 You cannot set a Date Variable to an Empty String since this is reserved for Strings.
__2 You cannot set a Date Variable to Null since only Variant Data Types can be set to a Null Value.
__3 You cannot set a Date Variable to 0 since that would return 12:00 A.M.
__4 You cannot set a Date Variable = Nothing because that is reserved for Object Variables.
__5 If these Date Variables are declared in a Form Module then once you close the Form they will automatically go out of Scope and lose their value. If they are declared as Public in a Standard Module, they will retain their value for the lifetime of the application which could be a problem.
__6 You can convert the Variable to a Variant then assign it to vbNull as in below, but then it simply returns 12/31/1899.

MyDate = CVar(MyDate)
MyDate = vbNull
Debug.Print MyDate ==> 12/31/1899

__7 In short, I'm kind of stumped. I'm sure some of the other Experts/Moderators will shed some light on your dilemma and meanwhile I'll still be working on it. Please be patient.

[Pam Poulos]

Thank you for your response.

To shed a little more light, they are stored at public variables which is why I need to clear the values.

[ADezii]

Thank you for your response.

To shed a little more light, they are stored at public variables which is why I need to clear the values.

The closet solution that I can come up with is to Declare the Date Variables As Variants as opposed to Date/Time, and then set them to vbNull. The will then contain a value of, believe it or not, 1.

Dim MyDate As Variant
MyDate = #1/1/2007#
 
Debug.Print MyDate      'output: 1/1/2007
 
MyDate = vbNull
Debug.Print MyDate      'output: 1

[NeoPa]

...Or you could set a Variant variable as Null.
This cannot be done in a typed variable as ADezii says, but Variants are special cases for that and can be set to Null.
Testing for Null values in code can be done with the IsNull() function.

[ADezii]

I have a form where users input some dates, these dates are then written to date variables.

I need to clear out the date variables with either an empty string or a null value. Currently, I am using 12:00 AM to clear out the date value, however this is now causing confusion.

Can anyone tell me out to clear out the variable so it is empty.

Thanks so much

Pam

I think we have finally arrived at your solution although it didn't work a couple of hours ago. Please don't ask me why.

Dim MyDate As Variant
MyDate = #1/1/2007#
 
Debug.Print MyDate      'output: 1/1/2007
 
MyDate = Null
Debug.Print MyDate      'output: Null

[Pam Poulos]

Thanks everyone.

I will change them to variants. I truly appreciate your advise.

[NeoPa]

Not a problem Pam.
Glad you got what you were after :)