CODEWITHSUNDEEP
sqlms-accessvba
user1797935
user1797935

Reputation: 57

Access updating table with date using SQL

I have a table named Functions in MS Access and I would like the table to automatically update it self when a certain date reaches.

TBL_functions:

----------------------------------------------------
|tm_function | tm_start   | tm_end     | tm_archive |
 ---------------------------------------------------
|HV-1        | 09-03-2015 | 10-03-2015 |NO          |
|HV-2        | 09-03-2015 | 11-03-2015 |NO          |
|HV-3        | 09-03-2015 | 12-03-2015 |NO          |
|HV-4        | 09-03-2015 | 14-03-2015 |NO          |

So when the end date reaches today the row will change from archive no to yes

Maybe a on load VBA code that checks all dates when you open the form? Can somebody help me out?

Upvotes: 1

Views: 511

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19737

As HansUp suggested - have a query execute when the database opens.
This SQL will update the table where the date is equal or earlier to today.

UPDATE Functions
SET tm_archive = 'YES'
WHERE tm_end<=DATE()

Upvotes: 3

Related Questions