REPOST: Dealing with unknown dates

[Jim Shaw]

BlankI'm having trouble finding a consistent way, or even a set of working ways, of dealing with variables of type date when I want to indicate that there is no date. I keep getting "Invalid use of Null", "invalid syntax" or "Type mismatch" messages. I've tried in both date and string types, nothing seems to work consistently across my situations. I've used, Null, #00/00/00#, 0, "" ... nothing seems to work reliably.

In Access/XT I have:
a.. a table, with a date column, upon which I define DAO.recordset
b.. a form with VBA code that manipulates dates non-arithmetically
c.. a class object that has a date type property.
In my VBA code, if I assign the date in the table to the class object date property and the date field in the table has no date, I get "Invalid use of Null"

In my VBA code, I'd like to explicitly set the date object date property to a state indicating that there is no date specified, and then be able to test for that in my class object code.

When in the debugger I look at an empty date record, the date is displayed as "Null", but I'm not allowed to set it to null in the tables datasheet view, nor can I set it to zero!!??

If I have a valid date, everything goes well. But if I have no date, everything falls apart.

What is a good day to cope with a unknown or unspecified date situation?

Thanks
-Jim

 

[Ken Snell]

I often use a 0 or a negative integer for an "unknown" or "empty" date in my
code. I then test for that value to see if the field is holding a real date
or a fake date.

I've successfully used -100 as an "empty" date, but in reality you can use
any date number you want, so long as you'll never have a real date with that
number.

--
Ken Snell
<MS ACCESS MVP>

BlankI'm having trouble finding a consistent way, or even a set of working
ways, of dealing with variables of type date when I want to indicate that
there is no date. I keep getting "Invalid use of Null", "invalid syntax" or
"Type mismatch" messages. I've tried in both date and string types, nothing
seems to work consistently across my situations. I've used, Null,
#00/00/00#, 0, "" ... nothing seems to work reliably.

In Access/XT I have:
a.. a table, with a date column, upon which I define DAO.recordset
b.. a form with VBA code that manipulates dates non-arithmetically
c.. a class object that has a date type property.
In my VBA code, if I assign the date in the table to the class object date
property and the date field in the table has no date, I get "Invalid use of
Null"

In my VBA code, I'd like to explicitly set the date object date property to
a state indicating that there is no date specified, and then be able to test
for that in my class object code.

When in the debugger I look at an empty date record, the date is displayed
as "Null", but I'm not allowed to set it to null in the tables datasheet
view, nor can I set it to zero!!??

If I have a valid date, everything goes well. But if I have no date,
everything falls apart.

What is a good day to cope with a unknown or unspecified date situation?

Thanks
-Jim

 

[Dirk Goldgar]

I'm having trouble finding a consistent way, or even a set of
working ways, of dealing with variables of type date when I want to
indicate that there is no date. I keep getting "Invalid use of
Null", "invalid syntax" or "Type mismatch" messages. I've tried in
both date and string types, nothing seems to work consistently across
my situations. I've used, Null, #00/00/00#, 0, "" ... nothing seems
to work reliably.

In Access/XT I have:
a.. a table, with a date column, upon which I define DAO.recordset
b.. a form with VBA code that manipulates dates non-arithmetically
c.. a class object that has a date type property.
In my VBA code, if I assign the date in the table to the class object
date property and the date field in the table has no date, I get
"Invalid use of Null"

In my VBA code, I'd like to explicitly set the date object date
property to a state indicating that there is no date specified, and
then be able to test for that in my class object code.

When in the debugger I look at an empty date record, the date is
displayed as "Null", but I'm not allowed to set it to null in the
tables datasheet view, nor can I set it to zero!!??

If I have a valid date, everything goes well. But if I have no date,
everything falls apart.

What is a good day to cope with a unknown or unspecified date
situation?

Thanks
-Jim

Any special reason your class object can't have this property be defined
as a Variant instead, with code in the Property Let procedure to enforce
that any non-Null value assigned to it is a date, or is convertible to a
date? Then you could use Null with impunity as it is meant to be
used -- as an indication that the value is unknown or unspecified.

 

[Jim Shaw]

BlankKen and Dirk: Thanks, and you both have provided good work-a-rounds that will work for me.

I guess I'm too much of a purist! Code logic should reflect what's really going on in the application domain. Guess I'll have to write an IsEmptyDate() function based on your suggestionsq, but I think MS could have easily expended IsEmpty() to cover dates too.

Microsoft moves in mysterious ways!

Thanks a lot guys!
Jim