Three fields to represent a date?

[Jennifer K.]

I am creating a table that needs to have the date (i.e. 01/31/2008) stored as
three separate fields as follows to be consistent with an older database.

Month, length of 2 and can have 01-12 or 99 for unknown month (i.e. 01).
Day, with a length of 2 and can have 01-31 or 99 for unknown day (i.e. 31).
Year, with a lenght of 4 or 9999 for unknown year (i.e. 2008).

Can these be recognized as dates (or components of dates) in an Access
table? Can there be 9's for unknowns and still be a date or recognize the
known date components.
If they cannot be dates then they should just be formatted as numbers?

Thanks,
Jennifer

 

[Danny Seager]

format the fields as numbers.. you can "make them a date" in a query using
the DateSerial() function

 

[John W. Vinson]

I am creating a table that needs to have the date (i.e. 01/31/2008) stored as
three separate fields as follows to be consistent with an older database.

Month, length of 2 and can have 01-12 or 99 for unknown month (i.e. 01).
Day, with a length of 2 and can have 01-31 or 99 for unknown day (i.e. 31).
Year, with a lenght of 4 or 9999 for unknown year (i.e. 2008).

Can these be recognized as dates (or components of dates) in an Access
table? Can there be 9's for unknowns and still be a date or recognize the
known date components.
If they cannot be dates then they should just be formatted as numbers?

Well, they certainly cannot be date/time fields; and they certainly should not
be named using the reserved words Month, Day and Year. Consider using
Number/Long Integer fields with other names (I'll use TheMonth, TheDay,
TheYear below). Could you explain why it "needs" to be stored in this manner?
It might have been necessary to do so with your older software but it
certainly isn't now! You can use the DateSerial() function to construct a
date, using IIF() to detect the 99's:

IIF([TheDay] = 99 Or [TheMonth] = 99 Or [TheYear] = 9999, Null,
DateSerial([TheYear], [TheMonth], [TheDay]))

will return NULL for a partially unknown date and the actual date otherwise.

 

[Jennifer K.]

Thank you both for your input and it follows what I had been planning to do.
It was extremely helpful. I advocated for storing the date as a date
but...change is hard.
Thank you again,
Jennifer

I am creating a table that needs to have the date (i.e. 01/31/2008) stored as
three separate fields as follows to be consistent with an older database.

Month, length of 2 and can have 01-12 or 99 for unknown month (i.e. 01).
Day, with a length of 2 and can have 01-31 or 99 for unknown day (i.e. 31).
Year, with a lenght of 4 or 9999 for unknown year (i.e. 2008).

Can these be recognized as dates (or components of dates) in an Access
table? Can there be 9's for unknowns and still be a date or recognize the
known date components.
If they cannot be dates then they should just be formatted as numbers?

Well, they certainly cannot be date/time fields; and they certainly should not
be named using the reserved words Month, Day and Year. Consider using
Number/Long Integer fields with other names (I'll use TheMonth, TheDay,
TheYear below). Could you explain why it "needs" to be stored in this manner?
It might have been necessary to do so with your older software but it
certainly isn't now! You can use the DateSerial() function to construct a
date, using IIF() to detect the 99's:

IIF([TheDay] = 99 Or [TheMonth] = 99 Or [TheYear] = 9999, Null,
DateSerial([TheYear], [TheMonth], [TheDay]))

will return NULL for a partially unknown date and the actual date otherwise.

 

[David W. Fenton]

Consider using
Number/Long Integer fields with other names

Why would you use Long for data that can't possibly overflow a
regular Integer field?

 

[John W. Vinson]

Why would you use Long for data that can't possibly overflow a
regular Integer field?

Habit. You're quite correct of course!