What is the best way to show text that can vary depending on conditions on a form
To explain ...
I have a form with an unbound box which shows the result of the following DMAX expression
=DMAX("[Date of Payment]","Income","[MemberIDFK] = Forms.formname.Member_ID")
This successfully shows the date of the most recent payment from a member.
I want to show text next to this box which states whether the members payments are up to date or overdue etc.
ie ..
if today's date - DMAX is less than 365 days the text would read "Paid Up to Date" (or something similar)
if todays date - DMAX is more than 365 days but less than (say) 450 the text would read "Subs Due"
if todays date - DMAX is more than 450 and less than (say) 730 the text would read "Over Due"
if today's date - DMAX is more than 730 the text would read "Lapsed"
Do I need to repeat the DMAX calculation, or can I just get the date of the last payment from the original unbound box which is a text box named Last_Payment
Ideally, I would also like to format the text colour to show the status / urgency of the payment situation. Presumably this can be done with Conditional Formatting.
Any help regarding the best way to achieve and show the variable text would be appreciated
Cheers
Stuart