I want access to fill a field with the current date

[Debbie]

I have a table with employee records. I have a field with their year of hire.
I would like to have a field in the table that will fill with current date
every time I open the table so I can calculate their years of service.
I created a field and tried Date() and Now() but neither will fill the whole
field with the date. They will add the current date when I enter new records
but won't update all records with current date. How can I make this happen?

Thanks,

Debbie

 

[GP George]

This would seem not to be the best approach to the requirement. Under most
circumstances, we do NOT store calculated values in tables, and that is
exactly what you are trying to do. There is also a troubling comment here in
that you state "every time I open the table ". In a well designed database,
one does not expect users--including the developer--to open tables directly.
You should almost always retrieve values from tables from within a form or
report. And that, in fact, is where the solution to your requirement lies.

Create a query based on this employee table. Add to it a field called
"CurrentDate" and populate that field with Date(), e.g. Date() as
CurrentDate.

Now, when you use that query as the recordsource for your form or report,
you have the required fields available.

HTH

George

 

[RonaldoOneNil]

Don't hold the current date in the table, just the hire date. You can then
calculate length of service in a query using something like (not tested)
LenOfService: DateDiff("yyyy",[HireDate],Date())

 

[John W. Vinson]

I have a table with employee records. I have a field with their year of hire.
I would like to have a field in the table that will fill with current date
every time I open the table so I can calculate their years of service.
I created a field and tried Date() and Now() but neither will fill the whole
field with the date. They will add the current date when I enter new records
but won't update all records with current date. How can I make this happen?

You don't.

If you fill the field in your table with today's date, that field will be
WRONG tomorrow. Today's date is simply not an attribute of an Employee!

To calculate the number of years in service as of the most recent service
anniversary, use a Query based on your table with a calculated field:

YearsOfService: Year(Date()) - [Year Of Hire]

assuming that you have the year of hire in a Number field. If (as would be
more typical) you store the hiring date in a Date/Time field, the expression
is a bit more complex:

YearsOfService: DateDiff("yyyy", [Date of Hire], Date()) - IIF(Format([Date Of
Hire], "mmdd") > Format(Date(), "mmdd"), 1, 0)

And (as noted elsethread) if you're using the table datasheet to interact with
your data... *don't*. Tables are of VERY limited functionality; you should
instead use Forms.

 

[Guest]

I have a table with employee records. I have a field with their year of
hire.
I would like to have a field in the table that will fill with current date
every time I open the table so I can calculate their years of service.
I created a field and tried Date() and Now() but neither will fill the
whole
field with the date. They will add the current date when I enter new
records
but won't update all records with current date. How can I make this
happen?

Thanks,

Debbie