Jennifer has attended:
Access Intermediate course
Date fields on forms
How do I set a date field on an Access form to change colour six months (for example) before it expires?
Can I use conditional formatting?
We have contract expiry dates as a field and I want to alert users to when they are about to end.
RE: Date fields on forms
Hi Jennifer, thanks for your query. Yes, that's do-able with conditional formatting. Here's an example to format the form text control "txtMyDate" if it's value is greater than 7 days. Right click on the control and choose conditional formatting, then change the Condition 1 dropdown to "Expression Is" and enter the following expression in the next field:
[txtMyDate].[Value]-Date()>7
You'll need to amend for your own test, but that should do the trick.
Hope this helps,
Anthony
RE: Date fields on forms
Hello Jennifer,
Here's some extra help on this topic...
If you want to use conditional formatting directly on the date field, open the form in design view, then simply right-click the date field, choose conditional formatting, then select 'Expression Is' from Condition 1 drop-down and enter the following expression into the field:
DateDiff("d",Now(),[tblBookSales!TransactionDate])<180
All you have to do is replace the table & field name with your own.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
RE: Date fields on forms
Hi Anthony,
This works very well. I'd like to add in a Category field I have so that it links to this date field.
The Category lists Contracts and Agreements. How do I link this expiry date format so that it kicks in for six months if the Category contains Contract and thirty days if the Category contacts Agreement?
Thank you
Jen
RE: Date fields on forms
Hi Jennifer. You'll have to play with it, but the basic principle is to use a mixture of AND and OR operators to bolt together the different tests. The expression is going to be something along these lines:
([txtMyDate].[Value]-Date()>7 AND [txtCategory]="Contract") OR ([txtMyDate].[Value]-Date()>7 AND [txtCategory]="Contract")
Anthony
