Help - Automatic Update a field based on a expiry date

[worldcert]

Hi Guys and Girls

I am struggling to find a solution that I am sure is quite simple for someone more experienced than me with Microsoft Access.

I issue certificates and have a certificate table in Access.

I have fields called "DateCertificateExpires" and "Status".

At the moment I have to manually update the status field when the expiry date is reached. The Status field values are "Valid","Withdrawn,"Expired"

Is it possible for this "Status" field to update itself to "Expired" when the expiry date has been reached.

Any help would be appreciated.

Regards

Grant

 

[John Big Booty]

You could trigger an update query, that checks the expiry date against the current date, using your switchboard's (or any other form that you know is opened on DB start up) On Load event.

The query might look something like;

UPDATE TBL_Certificate SET TBL_Certificate.Status = "Expired"
WHERE (((TBL_Certificate.DateCertificateExpires)=Date()));

 

[DCrake]

If the expiry period is a set number of days from date of issue then you could use a query to determine this. Lets say they expire after 1 year, then your query would have a column that was something like this

Expired:iif(DateAdd("y",1,[IssueDate]) < Date(),"Expired","Ok")

In other words if we add one year to the expiry date and compare it against today's date if it is less than today then it must have expired. However you could only do this on those records that do not have a renewal date.

 

[worldcert]

Unfortunately the certificates dont have a set period of time. I need to compare the expiry date to the actual date.

I took this code..

UPDATE TBL_Certificate SET TBL_Certificate.Status = "Expired"
WHERE (((TBL_Certificate.DateCertificateExpires)=Date()));

and added it to the Click Event so when I click on the field it would update.

I get a compile error : Expected End of Statement highlighted here

UPDATE TBL_Certificate SET

Tried a few other ways to word the code but cant get it to work.

Any ideas? thanks in advance.

 

[boblarson]

You should not be updating the table with this data, really. You should just be using a query to get the table with the additional field added in there.

 

[John Big Booty]

Unfortunately the certificates dont have a set period of time. I need to compare the expiry date to the actual date.

I took this code..

UPDATE TBL_Certificate SET TBL_Certificate.Status = "Expired"
WHERE (((TBL_Certificate.DateCertificateExpires)=Date()));

and added it to the Click Event so when I click on the field it would update.

I get a compile error : Expected End of Statement highlighted here

UPDATE TBL_Certificate SET

Tried a few other ways to word the code but cant get it to work.

Any ideas? thanks in advance.

The code I posted is the SQL for an update query, you could create yourself a blank query and then put it in SQL view and paste my code into it. You will however need to ensure that all the table and field names are valid.

Alternately you could use Docmd.RunSQL to run the SQL directly. Previous caveats still apply.

 

[boblarson]

And I will reiterate very strongly.

Unless you are using Microsoft Access 2010 with the new data macros, you really should NOT store the status in the table. You only DISPLAY it in a QUERY and you can use a QUERY where you would normally use the table.

 

[John Big Booty]

To expand on boblarson's comments, what you are doing is storing a calculated field. This is generally not considered best practice in Relational DB's, for a number of reasons;

1. The data to calculate the field is stored in the DB, so the field can be calculated at any time for display purposes.
2. Why waste space storing a value that can be calculated at any time.
3. and the kicker, there is no guarantee that the calculated field will be changed to reflect changes in the underlying data.

As an alternate consider an unbound text box or even a label who's content is determined in the On Current event of the form with reference to the expiry date.

For example;

If Me.DateCertificateExpires <= Date() Then
     Me.YourTextBoxName.Value =  "Expired"
Else
     Me.YourTextBoxName.Value = "Current"
End If

Or for a label;

If Me.DateCertificateExpires <= Date() Then
     Me.YourLabelName.Caption = "Expired"
Else
     Me.YourLabelName.Caption = "Current"
End IF

 

[worldcert]

Thanks guys I will investigate all your points and try and come up with a solution.

 

[namliam]

And I will reiterate very strongly.

Unless you are using Microsoft Access 2010 with the new data macros, you really should NOT store the status in the table. You only DISPLAY it in a QUERY and you can use a QUERY where you would normally use the table.

If this hasnt been stressed enough, I will repeat it again