Need help with message boxes displaying due to dates in a report

[helpme5]

Hi,

At the moment I have a report which shows vehicles, the action required (MOT in this case), the last renewal and the next renewal as shown below:

Basically, I need a message box to appear to the user when the next renewal date is within 60 days of today's date, for example it could say "A vehicle's MOT needs to be renewed soon - please check the MOT renewal report".

Any help would be greatly appreciated as I have no idea on how to do this.

Thank you.

 

[wdrspens]

One way would be to use the Conditional Formatting and highlight the various fields when the date is within 60 days of the finish date.
In design View of the report, right click on each field that you want to highlight, and chose "Conditional Formatting" then in the resulting Conditional Formatting dialogue select under "Condition 1" Expression Is" and then enter [NextRenewal]-60 and click on the 4th symbol (Fill/Back Color] and change it to, say, Yellow, and under Condtion 2 do the same only using -30 and change the fill/back colour to say red.
David

 

[helpme5]

Hi David, thanks for the reply.
I was just wondering - wouldn't the [Next Renewal]-60 just show the custom formatting on the date 60 days before the next renewal, and on no other days after that?

Thanks a lot

 

[wdrspens]

Yes it would, but if you use the < sign (I think it might be the > sign and I'm too tired to work it out, try it by trial and error) it would work for all dates that are less than (or more than) the appropriate amount. Sorry, I should have mentioned that.
David

 

[helpme5]

Thanks a lot David, you've been a real help. Will try out tomorrow.
Thanks again.

 

[wdrspens]

Actually I should also have mentioned that you probably need to do condition 1 -30 and condition 2 -60 since the test is made in the order of the conditions and if it is the other way round condition 2 will never arise, again sorry for the error.

 

[helpme5]

Ok cheers, no worries, just glad for the help!

 

[wdrspens]

Hi,
I have given your problem further consideration and wonder whether a query might not give a better result.
If you inserted a new column in the query called, say, Renew, with the following syntax:

Renew: IIf([NextRenewal]>Date()+28,IIf([NextRenewal]>Date()+56,IIf([NextRenewal]>Date()+84,"","Renew Shortly"),"URGENT Renewal"),"OVERDUE Renewal")

It would say "Renew Shortly" for MOT's due between 56 and 84 days, and URGENT Renewal for MOT's due between 28 and 56 days and OVERDUE Renewal for MOT's under 28 days. Of course, as far as the latter are concerned, once renewed the date would be a year away so the condition would no longer apply, but if not renewed the warning would keep on appearing.

That seems more like your requirement.


On another matter, do you really need a "Next Renewal" field? Surely it is simply the "Last Renewal" field plus one year.

David

 

[helpme5]

Thanks very much for you help David, I greatly appreciate it.

 

[gemma-the-husky]

i would definitely pop up a reminder form to show impending MOT renewals

then you could print it out, or email to someone