Dates, Dates, & More Dates 
Author Message
 Dates, Dates, & More Dates

I need help with dates!.  I have an Access database that
is riddled with dates in various tables.  All dates within
the data base were defined using the date data type.  This
data base is accessed exclusively by a custom VB
application.  Here's the problem:

The database application is very date dependent.  Anywhere
a date was defined in the database, the date data type was
used to read, write, and manipulate the dates within the
application.  Everything works fine with one exception:  
if the application removes a date from a table using
VBEmpty, "", or VBNull, and then later goes back to test
for an empty field-it is not empty.  Apparently setting a
date field to empty does not actually "clear" the field
but places some predefined value (12:00:00 AM) in the
field.  This, of course makes it impossible to test for
fields with no dates in them by using the isDate function
or the likes.   How is it that if I create a new record in
the DB that contains date fields that have not been
populated, I can test for an empty field without issue?  
Yet, once I enter a date into the field, and delete it, I
can no longer test for an empty field.  What is the
correct work-around for this problem?  I tried changing
the date type to text type within the DB but  the
application is starting to become very messy and
difficult, plus it doesn't make sense:  Why have a date
data type if you cant use it?  

I thank you in advance for any advice you can provide.



Mon, 30 Aug 2004 22:38:02 GMT  
 Dates, Dates, & More Dates
Dates are actually stored as numbers in Access. I suspect what's happening
is that the program is setting the date to 0 (which corresponds to midnight
on Dec 30, 1899). If that's not a legitimate date in your application, then
simply looking for WHERE MyDate = 0 should work.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele


Quote:
> I need help with dates!.  I have an Access database that
> is riddled with dates in various tables.  All dates within
> the data base were defined using the date data type.  This
> data base is accessed exclusively by a custom VB
> application.  Here's the problem:

> The database application is very date dependent.  Anywhere
> a date was defined in the database, the date data type was
> used to read, write, and manipulate the dates within the
> application.  Everything works fine with one exception:
> if the application removes a date from a table using
> VBEmpty, "", or VBNull, and then later goes back to test
> for an empty field-it is not empty.  Apparently setting a
> date field to empty does not actually "clear" the field
> but places some predefined value (12:00:00 AM) in the
> field.  This, of course makes it impossible to test for
> fields with no dates in them by using the isDate function
> or the likes.   How is it that if I create a new record in
> the DB that contains date fields that have not been
> populated, I can test for an empty field without issue?
> Yet, once I enter a date into the field, and delete it, I
> can no longer test for an empty field.  What is the
> correct work-around for this problem?  I tried changing
> the date type to text type within the DB but  the
> application is starting to become very messy and
> difficult, plus it doesn't make sense:  Why have a date
> data type if you cant use it?

> I thank you in advance for any advice you can provide.



Tue, 31 Aug 2004 00:01:21 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Date - date = date

2. Compare date in form with date/time in form with date/time in database

3. Dates dates dates

4. Date Time Picker & Null dates

5. *&*&*&* Date math question *&*&*&*

6. Date Difference to Return the most Current Date

7. Date/Time to date

8. VBA date query - not getting current date

9. Changing a Calculate field (Date) depending on Current date

10. ACC97:Date Conversion - Date to Double

11. Extracting the date out of a Date/Time field

12. Those Wierd Dates - Julian Dates

 

 
Powered by phpBB® Forum Software