Insertion of Year into table already created

[Tinara]

Help! I'm losing my mind! I have created a table in Access. After the table was created the field "Year Hired" was added. I only want to record the year. I cannot not get this to work and I know it's something simple! I keep getting error messages telling me the validation rule does not match the field property sheet. I have tried every single function DateAdd,DatePart and Year(date). nothing will generate the current year in the field.
!

Can someone, anyone PLEASE tell me what I'm doing wrong.

Tinara

[June7]

Do you want to update existing records? Do you have a field with date hired? If so, there is no need to save the year part to table. The year can be extracted from date at any time it is needed.

Do you want to automatically populate the field for a new record? Can set the textbox DefaultValue to Year(Date()).

[Tinara]

Do you want to update existing records? Do you have a field with date hired? If so, there is no need to save the year part to table. The year can be extracted from date at any time it is needed.

Do you want to automatically populate the field for a new record? Can set the textbox DefaultValue to Year(Date()).

It doesn't work! This is driving me mad! The task is to auto populate the field "Year Hired" with the current Year. The table is already created. What you suggested puts the date in for new records, but the records already created remain blank. :>(

[Tinara]

Yes the field is "Year Hired" needs to auto insert current year only. Dates are not referenced anywhere else in the table so can't "pull" year from another field!

[RagJose]

What type is your field "Year hired"? Your comment on June7's DefaultValue suggestion makes me believe you have used a Date/Time field type. If you want just year figures in it, it should be Number/Integer. Year(Date()) as DefaultValue will then work. To populate previous records, create and run a simple Update query:

UPDATE {table} SET [Year Hired] = Year(Date());

Hope to have helped.

[Tinara]

What type is your field "Year hired"? Your comment on June7's DefaultValue suggestion makes me believe you have used a Date/Time field type. If you want just year figures in it, it should be Number/Integer. Year(Date()) as DefaultValue will then work. To populate previous records, create and run a simple Update query:

UPDATE {table} SET [Year Hired] = Year(Date());

Hope to have helped.

I entered the command you gave me and it told me it is going to update zero records so what am I doing wrong? I have the field to be updated as [Year Hired]. The table is the Instructor information table. I am entering date as Year(#03/15/2012#)

[June7]

Is the field a number datatype and [Year Hired] is the name of field in your table? You want the year 2012 saved for all records?

UPDATE tablename SET [Year Hired] = 2012;

[Tinara]

Is the field a number datatype and [Year Hired] is the name of field in your table? You want the year 2012 saved for all records?

UPDATE tablename SET [Year Hired] = 2012;

Yes! what does [QUOTE] REPRESENT?

[June7]

Don't understand your question.

[RagJose]

Tinara, can you import just this Instructor Information table into a blank mdb (Access 2002 format) and send us this file so we can take a closer look? In the imported table, you can delete columns that hold confidential data if any.