Multiple date formats in a Table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: harvestman (anonymous_at_discussions.microsoft.com)
Date: 04/07/04 

Date: Wed, 7 Apr 2004 01:35:12 -0700

I have a quite large Access db with about 6,000 records,
each with 32 fields divided into three tables. I'm having
a problem with dates in one of my tables, especially since
I'm trying to get the dates listed in chronological order.

My db relates to my Uni thesis here in Oz, recording
occurrences of invertebrate species with dates running
from 1883 to 2004. I have reconfigured my short date under
Regional Settings Properties to read as: dd-mmm-yyyy
(e.g., 07-Apr-2004). However, the problem I have is the
fact that for about a quarter of my records I only have
a "mmm-yyyy" date or "yyyy" date and have no date
information at all for about a third of my records.

If I select "Date/Time" as my data type in the field I
have shown for the Collection Date, I lose all my "mmm-
yyyy" and "yyyy" dates. Similarly, under the General Tab
for Field Properties, in the first property: "Format", I
can only select a single (particular) date style
e.g., "short date", rather than the three date style
values I want to select... i.e.,
dd-mmm-yyyy e.g., 07-Apr-2004
mmm-yyyy e.g., Apr-2004
yyyy e.g., 2004

So if anyone can advise me here, it would be much
appreciated... and in the instances where I have no date
info, can someone please advise if I should leave the
value for this field blank, or write something like "null"
or "unknown".

Many thanks...