Year()

[Mike]

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!

 

[John W. Vinson]

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]

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!

 

[Mike]

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

 

[Mike]

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

 

[Graham Mandeno]

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.)

 

[Graham Mandeno]

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.)