98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Access Training and help » Date fields on forms
Date fields on forms
Resolved · Medium Priority · Version 2007
Mon 9 Jan 2012, 10:12Delegate Jennifer said...
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.
Wed 11 Jan 2012, 09:55Trainer Anthony said...
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
Wed 11 Jan 2012, 13:18Trainer Rodney said...
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
Fri 13 Jan 2012, 09:51Delegate Jennifer said...
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
Fri 13 Jan 2012, 10:20Trainer Anthony said...
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
Fri 20 Jan 2012: Automatically marked as resolved.
Training information:
See also:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Access tip:Deleting duplicate records from a tableYou cannot delete records tables where there duplicate records. A way around this is to create a new table which wont hold the duplicates. and then deleting the old table. |
