DateDiff

[geraldinegrieve]

I have a table in access that holds data on vehicle 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"
    If DateDiff("d", Me.Tax_Renewal_Date, Now()) < 15 Then
    MsgBox "Vehicles Due Tax Renewal", , "Renewal Due"
    If DateDiff("d", Me.Insurance_Renewal_Date, Now()) < 15 Then
    MsgBox "Vehicles Due Insurance Renewal", , "Renewal Due"
 
End If
 

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

[geraldinegrieve]

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

[MindBender77]

@geraldinegrieve
Have you tried using "ElseIf". I added this to the code you provided. I believe this should correct or issue but, is just air code.

Note: You could also make 3 IF statements for each condition as well.

HTH,
Bender

[geraldinegrieve]

Thanks for replying so quickly I did try an Else if but only does 1st message if true and stops and if 1st message not true then 2nd gets displayed and stops. I am not clear on your advice 3 seperate if statements because i did try that but all messages at all dates appeared Thanks in advance

[MindBender77]

@geraldinegrieve
Here is making the 3 IF statements. No matter if the first if is true or false, it will then move to the next IF. The same will happen for the second IF etc.

Bender

[geraldinegrieve]

That was also my understanding of it, but maybe I am putting it in the wrong place i have it in the Form oncurrent and it is not taking the criteria into consideration it is giving back all 3 messages regardless of dates

[MindBender77]

If that is the case, it seems your DateDiff statement may be to blame. Have you tried debugging line by line to see how the criteria is being handled? Also, this might do nothing but, in your DateDiff statement, remove the empty parenthesis after Now. Without knowing what your project does, the OnCurrent should be fine.

So something like this:
DateDiff("d", Date1, Now) < 15

HTH,
Bender

[geraldinegrieve]

Thanks a million for this I got it working so as if criteria is on first record then it gives message but if criteria is in any other record message doesn't appear until you click into the record so so close

[missinglinq]

if criteria is on first record then it gives message but if criteria is in any other record message doesn't appear until you click into the record

This would indicate that you're dealing with a Datasheet or Continuous View form, in which case it is only going to work as you've just described. The Form_Current event fires as you move to a record, so the warning for a given record won't pop up until you move the focus to that record. .

An alternative, for a Continuous or Datasheet view, would be to use Conditional Formatting to change the background color of the appropriate textboxes to indicate that the fee is due.

Welcome to Bytes!

Linq ;0)>

[geraldinegrieve]

Thanks I already have conditional formating just wanted the message as form opened but thanks a million for that info would make perfect sense