Date Question

S

Starwood

I have created a database to store photographs and one of the entries is the
date the photo was taken. Unfortunately, many times the date is not
complete. In some cases the day is missing, in some cases the day and month
are missing and in rare cases, the entire date is missing.

I have initially set up the date as three text fields to accommodate the
possibility of the missing values. Are there any other solutions to the
problem? I'd like to use a single date field if possible.

George L.
 
K

Ken Snell \(MVP\)

Unless you will always have complete dates, or unless you want to use a
"default" date (such as May 1, YEAR when all you know is the month and
year), your use of three separate fields is appropriate. If you try to store
the data in one field, you'll spend lots of time parsing and combining data
to make the "data value" -- and I see no benefit to doing this.

You can easily combine the three fields back into a valid date if you should
need it, but otherwise I'd keep the three fields and then design the
forms/reports appropriately to show the desired "date format" that you want
to show.
 
J

John W. Vinson

I have created a database to store photographs and one of the entries is the
date the photo was taken. Unfortunately, many times the date is not
complete. In some cases the day is missing, in some cases the day and month
are missing and in rare cases, the entire date is missing.

I have initially set up the date as three text fields to accommodate the
possibility of the missing values. Are there any other solutions to the
problem? I'd like to use a single date field if possible.

George L.
Click to expand...

An Access Date/Time field is actually stored as a number - a count of days and
fractions of a day (times) since an arbitrary start point. As such, it
corresponds to an exact point in time - and "January 1992" or "sometime in
1981-1982" isn't a valid date/time value!

I think your three fields are probably your best solution; you could store
integer months and days, and use combo boxes with values 0-12 and 0-31 to
enter them, with 0 standing for an unknown month or unknown day.

John W. Vinson [MVP]
 
Top