Auto fill field in a table by comparing a given date against the current date

[KPAW]

I am building a database to track contract of employees so that I can know which contracts are valid and which are expired.



My table has the following fields:

ID (Primary key)
Employee ID (Foreign key to link to the employee table)
Start_Date
End_Date
Status (Either valid or expired)

Challenge

I want when I enter the end date, the system checks the end date against the current date and fills in the status field with either valid or expired as appropriate. For instance if the contract end date is March 10,2016, the status must be filled in the word valid.

Thanks for any assistance

[alansidman]

Are you using a form to input data? if so, then create an =IIF statement; ie. =IIF(End_Date > DAte(), "Valid", "Expired"). Put this in your control for the Status on your form.

[KPAW]

Thanks for the solution, but my intention was not to use a form to input valid or expired.

Instead, what I want to have happen is that I enter the contract start and end dates, and the system must compare end date against current date and input valid if end_date > Date().

However, with the passage of time the end_date would be less than current Date, the value in the field should change to expired.

[alansidman]

It is not advisable to have users enter data directly into tables. There is to much risk to this methodology. Unless your data is coming from an outside (linked) source, you should be entering your data in a form. If you are using linked tables, then you will need to run an update query to change the information in your tables, however experienced Access users will not run calculations in tables, but will run queries and reports with the needed information generated within the query. In that manner, the expired/valid information is updated each time you run the query. It is important to understand that relational databases do not perform as a spreadsheet. That is a different mentality completely.