I have a database with future effective date field. What I wanted to do is to update the Effective date field with the future effective date data once it become effective automatically on 12 midnight everyday. Example: The future Effective date is 4/1/2004. At midnight when that date comes I want to move that date to the Effective Date field automatically without any human interaction and make the future effective date blank again. Is it possible? Any help will do. Thanks in advance.
Sure it's possible...anything is possible but you will need to do a couple of things.
You will need to detect midnight or the start of a new day and it must be done while the database is running and when the database is started as well (in case of power failure or database interruption). Using the On Timer event can do this by checking the computer's date every so often (every 30 minutes or every hour) then, firing a specific Function when the required condition is met (date has advanced).
Since the information that requires changing (Effective Date) lies within a table a special function would be one that opens a recordset for edit. Then search through the recordset and change any Effective Date (which meets your required condition(s)) with the Future Effective Date. This could be done through SQL as well.