How to extract the year from a date Field

  • Thread starter Thread starter apex77
  • Start date Start date
A

apex77

I have a field called 'InvoiceDate' and a filed called 'Invoice Year'. The
'InvoiceDate' field is formatted as 7/29/2008 and the 'InvoiceYear' field is
formatted as a number field. I am trying via update query to get just the
year into the 'InvoiceYear' field, bur am getting type failure error. Here is
the qyuery:

UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
[tblQuotaAppend]![InvoiceDate]
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));

Please help
Thank you
 
UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
Year([tblQuotaAppend]![InvoiceDate])
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));
 
Simple, bur exactly what I needed.
Thanks Dennis!!

Dennis said:
UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
Year([tblQuotaAppend]![InvoiceDate])
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));


apex77 said:
I have a field called 'InvoiceDate' and a filed called 'Invoice Year'. The
'InvoiceDate' field is formatted as 7/29/2008 and the 'InvoiceYear' field is
formatted as a number field. I am trying via update query to get just the
year into the 'InvoiceYear' field, bur am getting type failure error. Here is
the qyuery:

UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
[tblQuotaAppend]![InvoiceDate]
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));

Please help
Thank you
Click to expand...
Click to expand...
 
UPDATE tblQuotaAppend
SET tblQuotaAppend.InvoiceYear = Year([tblQuotaAppend]![InvoiceDate])
WHERE tblQuotaAppend.InvoiceYear Is Null AND InvoiceDate is not Null

HOWEVER, it really makes almost no sense to do this. You can always get the
invoice year by using the expression in your queries or on forms and reports.
Year([tblQuotaAppend]![InvoiceDate])
and you won't need to worry about the two fields getting out of synch if you
change one and don't change the other.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
Year([tblQuotaAppend]![InvoiceDate])
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));


apex77 said:
I have a field called 'InvoiceDate' and a filed called 'Invoice Year'. The
'InvoiceDate' field is formatted as 7/29/2008 and the 'InvoiceYear' field is
formatted as a number field. I am trying via update query to get just the
year into the 'InvoiceYear' field, bur am getting type failure error. Here is
the qyuery:

UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
[tblQuotaAppend]![InvoiceDate]
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));

Please help
Thank you
Click to expand...
Click to expand...
 
apex77 said:
I have a field called 'InvoiceDate' and a filed called 'Invoice Year'. The
'InvoiceDate' field is formatted as 7/29/2008 and the 'InvoiceYear' field is
formatted as a number field. I am trying via update query to get just the
year into the 'InvoiceYear' field, bur am getting type failure error. Here is
the qyuery:

UPDATE tblQuotaAppend SET tblQuotaAppend.InvoiceYear =
[tblQuotaAppend]![InvoiceDate]
WHERE (((tblQuotaAppend.InvoiceYear) Is Null));
Click to expand...


Whoa there. You should ***not*** be storeing derived values
in a table. It is a major violation of the relational
database rules of Normalization.

You can extract the year from a date field any time you wan
to display it in a form/report text box just by setting the
text box's Format property to yyyy

If you want to group by the year in a query or report, the
you can use Year(datefield) without ever having the year as
a separate field in a table.
 
Back
Top