Extract Year From Date Field?

[emclem]

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]

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]

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;

=[URL="http://www.techonthenet.com/access/functions/date/datepart.php"]DatePart[/URL]("yyyy", Me.YourDateField)

 

[John Big Booty]

...you can read about data normalisation here.

 

[Galaxiom]

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;

=[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]

Sorry my bad, yes that is a VBA refrence, use;

=DatePart("yyyy", [YourDateField])

 

[emclem]

Thanks for your help.

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


Thanks

 

[Galaxiom]

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

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