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. ((Current Date - Start Date)/(End Date - Start Date))*Contract Amt

2. SQL: Select some data with date criteria, but only by Date part of date field

3. DATE DATE DATE Filemaker and Month names

4. Extract Date only from Date/Time Field

5. comparing the sql date and sys date

6. Converting regular dates to real dates

7. Retriveing only the date part ofthe date!!!

8. How to convert US date to Intl date format

9. Using a table of dates verses SQL Date Functions

10. Radain dates in SQL 7 to Real dates

11. VarChar date to datetime without the date

12. Date From Date To


 
Powered by phpBB® Forum Software