Date/Time when only year is known

[Alriemer]

I have a table of 994 composers and conductors born between 1170 and 1975. For some of them, only the year of birth or death is known. The fields for DOB and DOD are formatted as Date/Time, which refuses 4-digit years. Does anyone know a way around this? I am reluctant to use arbitrary days and months (say 1/1/1170) given the fuzz that would introduce to the data.

Thanks
Alriemer

 

[SydneyGeek]

Not sure -- unless you know the date, anything you enter instead will be a fudge. Access (and any other app that does dates) assigns a unique integer to each date in its calendar. Just entering a year will usually default to 1 January.

Denis

 

[TryingToLearn]

Assuming the DOB is in the same column for each, it is possible to use VBA to change the format of the cell if the data is only 4 characters. Unfortunately it will take me longer to figure out the correct code than for someone else to see this and reply. But at least I'll learn from it!

HTH

 

[Dugantrain]

Here's an idea:
You could add two Yes/No fields to your table and call them something like "DOB_Known_Y_N", "DOD_Known_Y_N". If the actual Date is known rather than just the Year, then Check would be Selected, if not known, then not Selected. When you want to properly show your Data, then you could use a simple Iif function in a query:

BirthDate:Iif([DOB_Known_Y_N]=False, Year([DOB]),[DOB])

which would return the DD/MM/YYYY date if DOB is known and return just the YYYY if DOB is not known. Repeat for the DOD field.

 

[Alriemer]

The last sounds like the best approach. At the moment, mostly concerned with ease of entry. I'll experiment with dynamic fields.
Thanks much
Alriemer