Extract Year From Date Field? (1 Viewer)

emclem

New member
Local time
Today, 23:38
Joined
May 20, 2011
Messages
3
I am new to Access to please forgive me if the answer to my question is simple.

I have created a new table with the companies Job numbers, Date Entered, Client Name, and Project Name.

I really want to add a field at the end which automatically extracts just the year from the Date Entered coloumn - how do I do this?

I have worked out how to do this by running a querie but would like it to show up in the table as I am working on it.

Thanks in advance,

Em :)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:38
Joined
Jan 20, 2009
Messages
12,849
Storing the year in a field would be a breach of normalization because it is derived from the Date Entered field.

Usually the year is extracted from the other field as required.

BTW. Avoid using spaces in your names because you will always have to enclose them in square bracket.
 

John Big Booty

AWF VIP
Local time
Today, 23:38
Joined
Aug 29, 2005
Messages
8,263
As an addendum to Galaxiom's comments you can use the following in an unbound text box to display the year as derived from your date field;
Code:
=[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("yyyy", Me.YourDateField)
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:38
Joined
Jan 20, 2009
Messages
12,849
As an addendum to Galaxiom's comments you can use the following in an unbound text box to display the year as derived from your date field;
Code:
=[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("yyyy", Me.YourDateField)

Alternatively use the Year([fieldname]) function.

Note however that Me cannot be used in a ControlSource. It is a VB term that is not understood by Access objects. Just use the fieldname.
 

John Big Booty

AWF VIP
Local time
Today, 23:38
Joined
Aug 29, 2005
Messages
8,263
Sorry my bad, yes that is a VBA refrence, use;
Code:
=DatePart("yyyy", [YourDateField])
 

emclem

New member
Local time
Today, 23:38
Joined
May 20, 2011
Messages
3
Thanks for your help.

I am sorry to ask such a silly question but where do I put that expression?


Thanks
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:38
Joined
Jan 20, 2009
Messages
12,849
On a form or report as the ControlSource of a textbox.

If you just want it in the query enter this in the field cell of a query designer column

Code:
[NewFieldName]: DatePart("yyyy", [YourDateField])
 

Users who are viewing this thread

Top Bottom