I have a date/time field that is set with a custom format
of mmm-yy. If I enter a date in the past, say Apr-01,
Access will display the date as the current year--Apr-04.
Does anyone know what I can do to fix this?
April 2004 is not a date: it's a period. And Jet does not have any data
type that corresponds to a period of time.
Suggestions:
1) Use a text field. The least useful would be "April, 2001" because you
can't sort on it, subtract dates or anything. The most useful would be
"2001:04" because at least it will sort correctly. Whichever you go for,
validation will be difficult.
2) Use one numeric field. Either 2001.333 for a fraction of a year, or
24016 (e.g. 2001 * 12 + 4) for the number of months. Very easy to validate
and calculate. Needs a little bit of VBA in the user interface to hide it
from the users.
3) Use two integer fields, [04] and [2001]. Easy to validate, easy to sort,
group, and calculate. Little bit of a pain for the users, or you can hide
it in the interface.
Hope that helps
Tim F