Date Diff in VBA or Macro 1

[Grieve1]

I have a table in access that holds data on vehicles there are 3 fields holding dates on MOT, Tax and Insurance renewal I am looking for 3 different messages on opening if date of any is within next 2 weeks e.g.
If DateDiff("d", Me.MOT_Renewal_Date, Now()) < 15 Then
MsgBox "Vehicles Due MOT Renewal", , "Renewal Due"
End if
If DateDiff("d", Me.Tax_Renewal_Date, Now()) < 15 Then
MsgBox "Vehicles Due Tax Renewal", , "Renewal Due"
End IF
If DateDiff("d", Me.Insurance_Renewal_Date, Now()) < 15 Then
MsgBox "Vehicles Due Insurance Renewal", , "Renewal Due"

End If

Macro used was:
I have now tried this in a macro and works ok if 1st record holds the true data but not if it is the 2nd or any other record e.g.
Condition ([tax_renewal_Date]-Now())<15 Action Message Box
Condition ([MOT_renewal_Date]-Now())<15 Action Message Box
Condition ([Insurance_renewal_Date]-Now())<15 Action Message Box
SO CLOSE but not quite correct
now i have tried various variations of this but all messages appear at all times regardless of dates any help greatly apprecipated Thanks in advance

 

[randy700]

I can see no reason for your code to fail UNLESS the fields are not formatted as date fields.


Randy

 

[Grieve1]

Hi Randy Thanks for your reply all field datatypes are date (medium Format) Works if criteria on 1st record but if criteria on any other record you must click into field in record before message appears, I assumed that regardless of position of criteria code would read all records and flag up message if criteria true maybe I have code in wrong place it is in On current of Form Thanks agian for your reply