date/time format

[Guest]

hey - im trying to make a field "year published".
and when making a table its not available under the general formats, (where
you find long date, long time, short date etc) so i decided to customize the
field and enter the format: yyyy
but apparently when i record a record and enter a year it doesnt work out.
i found out i had to type for e.g. 2/11/2006, and then when u press enter it
will appear as 2006.
how could i just ... enter 2006 simply to display 2006 (i need to have it in
the date/format field - not a general number or text field)
many thanks in advance.

 

[John Vinson]

how could i just ... enter 2006 simply to display 2006 (i need to have it in
the date/format field - not a general number or text field)

You can't. Those are incompatible requirements.

A Date/Time value IS A DATE - a precise point in time. 2006 is not a
date; it's 365 dates, and more specifically, 365*24*60*60 seconds.

If the values in this field will just be years, why is it necessary
that it be stored in a Date/Time field?

John W. Vinson[MVP]

 

[Guest]

oh, i understand now
because i looked at the help files and the ones online too
and it says
yyyy Full year (0100 to 9999).
so i thought i could just type in that as a format for year under date

so what if i needed to calculate another field based on the year published,
say for e.g. "age of the book; how many years ago was it published" and
since the year published field will be a number field...
what will be the solution for that other field "age of the book"?

 

[John Vinson]

oh, i understand now
because i looked at the help files and the ones online too
and it says
yyyy Full year (0100 to 9999).
so i thought i could just type in that as a format for year under date

You can. But what's stored in the Date/Time field is a complete date.
You can set the Format of the field to just DISPLAY the year portion
of the date, but the whole date is there.

so what if i needed to calculate another field based on the year published,
say for e.g. "age of the book; how many years ago was it published" and
since the year published field will be a number field...
what will be the solution for that other field "age of the book"?

Simply calculate it:

Year(Date()) - YearPublished

Of course like all age calculations, this field should NOT be stored
in any table, but instead just calculated on demand.

John W. Vinson[MVP]

 

[Pat Hartman\(MVP\)]

Usually you see month and year published, not just year. You can enter
Jan/2006 and Access will assume the first of the month. When given 2 out of
the 3 parts of a date, the third can sometimes be derived. Play around. If
you enter Jan/06 - I believe the assumption will be Jan 6, 2006 so you will
need to enter the full four digit year.

What you need to understand is that dates are NOT stored as text strings.
They are stored as double precision numbers with the integer part
representing the number of days since Dec 30, 1899 and the decimal part
representing time of day. Negative dates are dates prior to Dec 30, 1899
which is referred to as the origin date (zero value). -1 is Dec
9,1899. -2 is Dec 28, 1899, etc.