Calculate age of specific data in a record?

[RogerC]

Can anyone tell me if the following 2 scenerios are possible in Access?

1) I have a form that populates a field with the word 'Pending' or 'Closed' when the user selects either choice from a drop-down. Whenever 'Pending' is selected, I would like another field to count the number of days that the field remains 'Pending'. At some time in the future, the user will change the status from 'Pending' to 'Closed'. In other words, I need to know how long (in days) the record remains in the 'Pending' status.

If that can be done...

2) If the age of the 'Pending' record reaches 14 days, can a macro be triggered that automatically emails the person who originally created the record (their name will be stored in another field in the same record)?

Thanks!

 

[SydneyGeek]

You could create another Date field called StatusChange. When you select the drop-down, you can use code to push the current date to that new field.
Then, you can either query the data set to bring up "Pending" records over 14 days old, or set a hidden field / label on the form to display with a message.

Create the field in the appropriate table, then add it to the form. You can choose whether to display it or leave it hidden. Maybe displaying it would be the best for now.

To push the date to your field, from your Status combo, try this:
1. Design view in the form, Properties of the combo, click the Events tab.
2. Double-click the After Update event. you'll see [Event Procedure].
3. Click the builder (...) button to display the code module.
4. Enter this one-liner: [StatusChange]=Date()

Close and save the form. Now, whenever the status is changed, the current date will populate the StatusChange field.


Denis

 

[RogerC]

Thanks Denis! Your solution worked perfectly! I am now able to display the age of each record in the report.

Do you know if the concept in my second question can be done? If it can, my report would be completely automated and this would save me a great deal of time every day;

2) If the age of the 'Pending' record reaches 14 days, can a macro be triggered that automatically emails the person who originally created the record (their name will be stored in another field in the same record)?

 

[bat17]

one quick question to clarify a point, Do you only want to email on the 14th day or do you want to email for every record that is 14 days or older?

Peter

 

[RogerC]

For all records that are older than 14 days, I want to send a daily email (once every day) to the person who created the record, until the status changes from 'Pending' to 'Closed'.

 

[bat17]

Not tested, but the following code should give you a start.
create a query to filter for the records you want to email about (you can probably use the one you already have) the records will need to have the email address as well!

I have assumed a field called strEmailAddress to hold the address and a field called ID to hold the Record ID.
if you are using a version of Access higher than A97 you will need to select the DAO object libary
Open any module and >Tools>references... and in the dialog box scroll down untill you find the Microsoft DAO object libary and select it.

save this code in a module and save the module with a differnt name to the sub (or access will sulk )

Sub testMail()
Dim olApp As Object, olMail As Object
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(olMailItem)

Set dbs = CurrentDb()
' the name of your query here
strSQL = "SELECT * FROM zzzMailTest"
Set rs = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
Do While Not rs.EOF
   With olMail
      .To = rs![strEmailAddress]
      .Subject = "Over Due"
      .Body = "you account is overdue, Account number " & rs![ID]
      .DeleteAfterSubmit = True
      .Send
   End With
   rs.MoveNext
Loop
Set rs = Nothing
Set dbs = Nothing
Set olMail = Nothing
Set olApp = Nothing
End Sub

HTH

Peter