Changing a Number to Equal Date

[GIraffe]

I currently have a column for "Year". It is typed as "Number" because I
could not figure out another way to just have a year appear. So in my year
field, I have 2007. Because it has been decided this database now needs to
do calculations with this "year" field to produce another date. I need to
convert the number 2007 to year 2007. I tried using CDate, however it gave
me 6/29/1905 (which I think is 2007 days from 1/1/1900?). Any thoughts?

Thank you.

 

[Douglas J. Steele]

What date do you want when all you have is a year? A date requires year,
month and day.

If you're saying that you have a date field AND the year field in your
table, get rid of the year field. In a query, add a computed field that uses
the Year function on your date field, and use the query wherever you would
otherwise have used the table.

 

[GIraffe]

The date I would use for the calculation is 10/1/2007. All I have,
currently, is a "year" field that's a number, if I change it to date/time,
all my 2007's convert to 6/29/1905. Writing this out, I see now I could do
an update query and change all 6/29/1905 to 10/1/2007, then do my
calculations. Once I change it to a date/time, then from here on out, it'll
be a date entry.

Aside, is there NO way to enter JUST a year in a field in Access?

 

[John W. Vinson]

I currently have a column for "Year". It is typed as "Number" because I
could not figure out another way to just have a year appear. So in my year
field, I have 2007. Because it has been decided this database now needs to
do calculations with this "year" field to produce another date. I need to
convert the number 2007 to year 2007. I tried using CDate, however it gave
me 6/29/1905 (which I think is 2007 days from 1/1/1900?). Any thoughts?

Thank you.

A Date/Time field is a precise point in time. 2007 is not and cannot be a date
- you need the date field to specify some day during the year. If it doesn't
matter which day, you could just pick one - say January 1st - and use the
DateSerial function:

DateSerial([Year], 1, 1)

Note that Year is a reserved word and not a good choice of a fieldname. If you
use it, be sure to always enclose it in [brackets].

John W. Vinson [MVP]

 

[John Spencer]

Yes, use a text field or a number field. Year is not a date, Day is not a
date, month is not a date, you need all three.

You can extract the parts of the date from a date field using the Year,
Month, or Day functions. So if you need to know just the year of a date

Year([SomeDateField]) returns just the year number.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..