How to "cancel" a Date variable ?

[tedsmith]

I ran across a slight problem with a variable declared as a date.
I am checking in a loop through many of database records that three dates are within a certain pattern for a number of different records.
The time/date fields are read from 3 consecutive records into Date/time declared variables PreviousDate, CurrentDate and NextDate
Some records may have dates in some of them and some may not because there may not be 3 records.

When the three dates are equally spaced then the activity is regular.
When say the previous record hasn't been entered the Current Date is the first occasion
When say the last record isn't entered the current activity is the last to happen.

The problem is how to set each Date variable to effectively zero so when the datetime comparison is done each time in the loop it just doesn't think one of the variables is Midnight or belonging to a record in the previous step in the loop and make a wrong calculation.

I find that presetting say PreviousDate = 2 each step of the loop sets it to 1/1/1900 then if I include
If PreviousDate > 2 Then .... before the date comparison step, it works.

IsDate(PreviousDate) always returns true

Is there a more correct or elegant way of effectively "resetting" a date variable to a value that cant be confused with a current date?

 

[HughLerwill]

>IsDate(PreviousDate) always returns true
If PreviousDate is a Date variable then IsDate will always return True, because a Date variable can only contain a valid date; if you try to set it to a value which is not a valid date you'll get an error. IsDate is often used to check if a Text variable contains a valid date value before trying to assign it to a Date variable and so avoid the error.

A Date variable is underlaid by a Double value, Days to the Left of its decimal and fractions of a day to the right, when Dimmed/ unassigned it has a value of Cdbl(MyDate) = 0, after assignment you can set it back to 0 (30 Dec 1899 00:00) with MyDate=0.

So instead of checking your Date varible with IsDate you should be checking it with Cdbl(MyDate)>0 to see if it should be considered.

 

[dilettante]

This is the sort of thing we have Variants for, since they can accept Null as a value.

 

[tedsmith]

Thanks
I hadn't thought of using Cdbl on a date!

I suppose what I was doing (setting MyDate=2) was effectively the same thing and checking the date variable directly as a number. If it was greater than 2 then it would be more likely to be a date.
To be sure I probably should use
If Cdbl(MyDate)>0 And IsDate(MyDate)=True Then

 

[Hypetia]

>If Cdbl(MyDate)>0 And IsDate(MyDate)=True Then

If MyDate is a Date variable, then IsDate(MyDate) will always return True. It is helpful if you are testing a if a variant or string contains a valid date as mentioned above.

Furthermore, CDbl is also not required. You can treat a date variable just like a numeric (Double) data type.

So, you can use:
[tt]If MyDate > 0 Then[/tt]

Or even better:
[tt]If MyDate Then[/tt]

This will validate all initialized dates before or after December 30, 1899, midnight.