Input mask for date field -- year only

[beth.morrison]

I'm trying to create a date/time field that will contain only a
four-digit year, e.g., "2002." I've tried using the input mask
0000;0;_ and format yyyy. But when I use that and attempt to enter
"2006" in the field, it says "The value you entered isn't valid for
this field."

Does Access assume a year-only field would be a number field, or am I
making a n00b error? Thanks....

 

[fredg]

I'm trying to create a date/time field that will contain only a
four-digit year, e.g., "2002." I've tried using the input mask
0000;0;_ and format yyyy. But when I use that and attempt to enter
"2006" in the field, it says "The value you entered isn't valid for
this field."

Does Access assume a year-only field would be a number field, or am I
making a n00b error? Thanks....

Sorry, a year is not a valid entry for a Date/Time datatype field.

A valid date entry for a Date/Time field must include a month day and
year.
Access will guess (if you don't use an Input Mask) if you enter 1/5
that you mean 1/5/2006 (the current year) or if you enter 7/2006 you
mean 7/1/2006, but there is no way to simply enter 2006 as a valid
date.

If you only want the Year, than change the field datatype to Integer.

Perhaps you should let us know why you want to do this, and someone
will tell you how.

 

[onedaywhen]

I'm trying to create a date/time field that will contain only a
four-digit year, e.g., "2002."

What are we taking about here: data capture/display in the front end or
table design?

The year 2002 would best be modelled in Access/Jet terms, with its one
second granularity for DATETIME data, as the duration

start_date = #2002-01-01 00:00:00#
end_date = #2002-12-31 23:59:59#

This comes in handy when comparing other DATEIMTE durations e.g. to
determine whether they occur (entirely) within a given year.

In terms of the front end, I guess it would be fixed width four
character text with a limited range of values e.g.

BETWEEN '1990' AND '2099'

Data going in:

DATESERIAL(CLNG(front_end_value), 1, 1) + TIMESERIAL(0, 0, 0) AS
start_date, DATESERIAL(CLNG(front_end_value), 12, 31) + TIMESERIAL(23,
59, 59) AS end_date

Data going out:

FORMAT$(start_date, 'yyyy') AS front_end_value

The above is SQL code rather than front end code but I trust you get
the idea.

Jamie.

--

 

[beth.morrison]

In terms of the front end, I guess it would be fixed width four
character text with a limited range of values e.g.

BETWEEN '1990' AND '2099'

Thanks! Front-end is what I was going for. We have used text but
didn't think of restricting the range (I like to idiot-proof things for
my data entry people).

 

[beth.morrison]

Fred, thanks -- that was exactly what I needed to know. I have another
field that requires only a month and day, but must not contain a year,
and now I know to create that as a text field also.

Thanks all!