Year()

I know how to extract the year from a date field in a form or query using the
Year function. But how would I do that in a table? I need to keep the year as
a field in the table. If I can't do it directly in a table, can I create an
expression in a form or query, then use the result to update a field named
Year in the table?

Thanks in advance for the advice!

On Sun, 10 May 2009 15:18:58 -0700, Mike
wrote:

I know how to extract the year from a date field in a form or query using the
Year function. But how would I do that in a table? I need to keep the year as
a field in the table. If I can't do it directly in a table, can I create an
expression in a form or query, then use the result to update a field named
Year in the table?

Thanks in advance for the advice!

If you have the date in the table, then you do NOT need to - nor should you -
store the year. If you JUST want to store the year (and no date) then you
could just use an Integer or Long Integer number field - 2009 is a perfectly
valid number, after all. But if you have a date field #5/10/2009# then you can
use a query calculating the year as needed.

If you have both the year field AND the date field, then there'd be nothing to
prevent discrepancies, e.g. having #5/10/2009# in the date field and 1836 in
the year field!
--

John W. Vinson [MVP]

Hi John:

I wanted to store the year in order for the client to use Filter by Form
using just the year rather than using greater than or equal to Jan 1 and less
than and equal to Dec 31. That was the request from the client.

Mike

"John W. Vinson" wrote:

On Sun, 10 May 2009 15:18:58 -0700, Mike
wrote:

I know how to extract the year from a date field in a form or query using the
Year function. But how would I do that in a table? I need to keep the year as
a field in the table. If I can't do it directly in a table, can I create an
expression in a form or query, then use the result to update a field named
Year in the table?

Thanks in advance for the advice!

If you have the date in the table, then you do NOT need to - nor should you -
store the year. If you JUST want to store the year (and no date) then you
could just use an Integer or Long Integer number field - 2009 is a perfectly
valid number, after all. But if you have a date field #5/10/2009# then you can
use a query calculating the year as needed.

If you have both the year field AND the date field, then there'd be nothing to
prevent discrepancies, e.g. having #5/10/2009# in the date field and 1836 in
the year field!
--

John W. Vinson [MVP]

Hi Mike

There is still no need to *store* the year. Just include it as a calculated
field in the recordsource of your form.

OrderYear: Year([OrderDate]

(If your form is using the table directly as a RecordSource, then create a
query with all the required fields (including this calculated one) and bind
your form to the query instead.)

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Mike" wrote in message
...
Hi John:

I wanted to store the year in order for the client to use Filter by Form
using just the year rather than using greater than or equal to Jan 1 and
less
than and equal to Dec 31. That was the request from the client.

Mike

"John W. Vinson" wrote:

On Sun, 10 May 2009 15:18:58 -0700, Mike
wrote:

I know how to extract the year from a date field in a form or query
using the
Year function. But how would I do that in a table? I need to keep the
year as
a field in the table. If I can't do it directly in a table, can I create
an
expression in a form or query, then use the result to update a field
named
Year in the table?

Thanks in advance for the advice!

If you have the date in the table, then you do NOT need to - nor should
you -
store the year. If you JUST want to store the year (and no date) then you
could just use an Integer or Long Integer number field - 2009 is a
perfectly
valid number, after all. But if you have a date field #5/10/2009# then
you can
use a query calculating the year as needed.

If you have both the year field AND the date field, then there'd be
nothing to
prevent discrepancies, e.g. having #5/10/2009# in the date field and 1836
in
the year field!
--

John W. Vinson [MVP]