Problem checking two fields

[RuralGuy]

Again, it will execute the Else part of that statement *only* if the initial Len() statement fails. I don't think that's what you want to happen.

 

[RuralGuy]

BTW, I should mention that the Else clause is not necessary if there is no code in the Else area.
If
End If
...is just fine for VBA.

 

[crann]

Ok thank you ive commented that Else out (for testing) and now is looking at both fields before displaying the message box.

 

[RuralGuy]

It probably should be replacing the Else with an End If and remove one of the End If's later on. That way it will always test both.

 

[RuralGuy]

When you're done, your code should look something like the following:

Private Sub Form_Open(Cancel As Integer)
   On Error GoTo err
   Dim db As DAO.Database
   Dim Rst As DAO.Recordset
   Set db = CurrentDb
   Set Rst = db.OpenRecordset("tblTenancies")
   If Rst.EOF Then Exit Sub

   Do Until Rst.EOF
      If Len(Nz(Rst!FireCheck)) > 0 Then
         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then
            MsgBox Rst!FireCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
         End If
      End If
      If Len(Nz(Rst!GasCheck)) > 0 Then
         If DateDiff("d", Now(), Rst!GasCheck) < 30 And DateDiff("d", Now(), Rst!GasCheck) < 30 Then
            MsgBox Rst!GasCheck & " : Next Safety/Legal Check(s) Due! (Within 30 Days) ", vbOKOnly, "ALERT! Safety/Legal Check(s) Due "
         End If
      End If
      Rst.MoveNext
   Loop
   On Error Resume Next
   Set db = Nothing
   Rst.Close
   Set Rst = Nothing
   Exit Sub
err:
   MsgBox err.Number & ": " & err.Description

End Sub

 

[crann]

Ah sorry yes I think it is now displaying both dates if they are on the same record but if I have one record with a FireCheck on and a different one with a GasCheck on its only displaying the fire one

 

[RuralGuy]

The code I posted should examine *both* fields for all records. Are you saying it does not do that?

 

[crann]

No your code works perfect, I had tried to change mine myself but I had only changed the Else to one End If.

I see your Code has a double End If but then only ends with two

Thanks so much, Ive learnt a lot on this one task. The only thing I lost is why we had to place a double End If after the FireCheck code and not just a single End If

 

[RuralGuy]

The indents show you which End If goes with which If. Here's your two If's:

      If Len(Nz(Rst!FireCheck)) > 0 Then
         If DateDiff("d", Now(), Rst!FireCheck) < 30 And DateDiff("d", Now(), Rst!FireCheck) < 30 Then

 

[crann]

Ah OK, that makes sense.

All working great could you just let know is it possible to ass the Msg box to display the Dates Due in order rather than as the records are stored or do I need to somehow order the date fields in the data table?

thanks

 

[RuralGuy]

You should be using a query and sort it any way you want.