Changing a Number to Equal Date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GIraffe - 30 Oct 2007 19:20 GMT

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.

Reply to this Message

Douglas J. Steele - 30 Oct 2007 19:37 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>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
[quoted text clipped - 7 lines]
>
> Thank you.

Reply to this Message

GIraffe - 30 Oct 2007 19:48 GMT

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?

> What date do you want when all you have is a year? A date requires year,
> month and day.
[quoted text clipped - 15 lines]
> >
> > Thank you.

Reply to this Message

John Spencer - 30 Oct 2007 20:12 GMT

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.

Signature

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

> 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,
[quoted text clipped - 31 lines]
>> >
>> > Thank you.

Reply to this Message

John W. Vinson - 30 Oct 2007 20:05 GMT

>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
[quoted text clipped - 4 lines]
>
>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]

Reply to this Message