Date field format

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

The date field appears to require the day, month, and year. I'm setting up a
catalogue of items where there is an "acquired date" field. Some items have
day, month, year and some items have month, year and some items have year
acquired only. Is there a way to keep it as a date field but only enter part
of the known dates? If not, then I assume it has to change to a text field.
If that is the case is there a "better" way to enter the dates so that if
needed, it could be queried or sorted as a date field? Examples coming to
mind:
year-month-day: 2009-12-01
year-month (no day): 2009-11-00
year only 2009-00-00

Thanks!
 
There's no way to use a date field unless you give it an entire date. That's
because under the covers, the date is stored as an eight-byte floating point
number where the integer portion represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents the time as a
fraction of a day.

You'll either have to use default month and/or day if you don't know it, or
use a text field.
 
I'd prefer to keep it a date field and if the day and month isn't known, make
it 1/1/2009 for example. If the day isn't known, make it the first day of the
month. If the entire date isn't known, leave it null.
 
You can use three Number fields to do this, but you will need to take care to
ensure valid entries.

TheYear
TheMonth
TheDay

Now you can combine those as needed to generate a full date.

Another option is to use a date field and always enter a full date and then
have an additional field named ApproximateDate and store Full, Year Only,
YearMonth Only or some other indicator. For Year only you would enter Jan 1
of July 1 in the date field (plus the year). For Year and month enter the
year and month and 15 for the day.

Other schemes are possible.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top