entering past dates

P

Pat

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?
 
D

Duane Hookom

Access assumes you are typing in April 1st if you type in Apr 01.
You can't get around entering at least a month and day of the month into a
date field. If you omit the year part, Access will assume the current year.
 
T

Tim Ferguson

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top