Checked Box + Date = Checked box

[ItsATJ]

I am trying to get a "Certified" check box to check itself based on two pieces of information. If the person has completed Phase1 training (a check box) and if they have been to their alt site within the last year. If both of these are yes, then it should check the "Certified" box. Here is the code I have attached to the "Certified" box that doesn't work:

Private Sub ValidateCert()
  If Me.chk_Phase1.Value = -1 Then
    If Now() - Me.txt_Visit.Value < 365 Then
      Me.chk_Cert.Value = -1
    Else
      Me.chk_Cert.Value = 0
    End If
  Else
    Me.chk_Cert.Value = 0
  End If
End Sub

Thanks for helping.

[orange]

I usually use techonthenet to find info about msaccess functions.

http://www.techonthenet.com/access/f...e/datediff.php
http://www.techonthenet.com/access/f.../date/date.php
http://www.techonthenet.com/access/f...te/dateadd.php

[ItsATJ]

Thanks for the help, but I am unsure how/where I need to use - DateAdd ("d", -365, Date()) - if that is even the correct syntax. All the examples have a date in the final field.

[orange]

In plain English what is it you are doing? Let's make sure we are talking the same functionality before any programming.

[ItsATJ]

I have a sheet that tracks the training of my personnel. In order to be a Certified Team Member, they need to accomplish a computer based training once (Phase 1 Training) and they also must visit their relocation site annually for a familiarization tour. (Phase 2 Training).

So, on my form, I have a check box for Phase 1 Training, a date Field for Phase 2 Training and a check box for Certified. If they accomplish Phase 1 Training and have not done a site visit, then they are not a Certified Team Member and the Certified box would not be checked. If the go to the site, but don't accomplish their Phase 1 Training, then, again the Certified box would not be checked. But, if they have accomplished their Phase 1 Training and they have been to the site within 1 year, then the Certified box would get checked. After 1 year and 1 day, if they don't return to the site, the the Certified box will become unchecked.

I am not sure if the Certified check box properties is the best place to accomplish this, or if there is a better method. I need to pull reports of the number of Certified people on a regular basis, and if the code is on the form, does that mean that I have to "visit" each person's form to update the Certified status?

[orange]

Sounds like this is what you're looking for (untested)

Private Sub ValidateCert() 
If Me.chk_Phase1.Value = -1 Then     
    If (DateDiff,"d", Me.txt_Visit.Value,Now()) < 365 Then    
     Me.chk_Cert.Value = -1    
    Else     
     Me.chk_Cert.Value = 0    
   End If   
Else  
   Me.chk_Cert.Value = 0  
End If 
End Sub

I would also recommend you use True for the -1 and False for 0. I realize these are equivalent, but to me at least the T/F seem "easier".

[ItsATJ]

That worked. Thanks.

[orange]

You are welcome. Glad to help. Good luck with your project.

[ItsATJ]

Actually, to be accurate, I used:

Private Sub ValidateCert()
  If Me.chk_Phase1.Value = -1 Then
    If DateDiff ("d", Me.txt_Visit.Value, Now()) < 365 Then
      Me.chk_Cert.Value = -1
        Else
      Me.chk_Cert.Value = 0
    End If
  Else
    Me.chk_Cert.Value = 0
  End If
End Sub

The open parentheses was in the wrong place. Again, Thanks for all your help.

[ItsATJ]

After using the code for half a year, I have run into a problem.

When the Site Visit date goes beyond 1 year, the Certified check box won't become unchecked without manual intervention. (I have been unchecking, then rechecking the Phase I check box to force the update.) Is there a way to get Access to "monitor" the two fields and if the date drifts out of the 1 year window, it will uncheck the Certified box automatically? Perhaps there's a macro that can be activated to force an update.

Thanks for any assistance

[ItsATJ]

Bump. Any thoughts?

[orange]

Please state the condition involved in plain English. Give a couple of examples.

[ItsATJ]

I have a sheet that tracks the training of my personnel. In order to be a Certified Team Member, they need to accomplish a computer based training once (Phase 1 Training) and they also must visit their relocation site annually for a familiarization tour. (Phase 2 Training).

So, on my form, I have a check box for Phase 1 Training, a date Field for Phase 2 Training and a check box for Certified. If they accomplish Phase 1 Training but have not done a site visit, then they are not a Certified Team Member and the Certified box would not be checked. If they go to the site, but don't accomplish their Phase 1 Training, then, again the Certified box would not be checked. But, if they have accomplished their Phase 1 Training and they have been to the site within 1 year, then the Certified box would get checked. After 1 year and 1 day, if they don't return to the site, the the Certified box should become unchecked.

Here is the code we used on the form:

Private Sub ValidateCert()
   If Me.chk_Phase1.Value = -1 Then
     If DateDiff ("d", Me.txt_Visit.Value, Now()) < 365 Then
       Me.chk_Cert.Value = -1
         Else
       Me.chk_Cert.Value = 0
     End If
   Else
     Me.chk_Cert.Value = 0
   End If
 End Sub

The code that is listed above will run when either of the fields are updated, however, if the date of site visit drifts outside of the one year window, the Certified box remains checked. I have no code in place to verify that a person remains Certified. When I pull the report of all the non-certified personnel , it will not list people who are no longer certified due to a site visit in excess of one year. I am looking for some method to have access re-certify each person's certification daily, either automatically or by manually prompting it by pushing a button.