Date fields on forms

Resolved · Medium Priority · Version 2007

Mon 9 Jan 2012, 10:12 Delegate 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:55 Trainer 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:18 Trainer 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:51 Delegate 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:20 Trainer 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 courses

	98.8 % recommend us
	More live stats...

Training information:

Access tip:

Deleting duplicate records from a table

You 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.

1. Use a make-table query based on this table only. IMPORTANT - Ensure that you include all of the fields from your original table or you may loose data.

2. Open the query's property sheet by using VIEW, QUERY, PROPERTIES, and set the Unique Values property to Yes

3. Because you have selected the Unique Values to Yes when you run the query, Access creates a new table without duplicate records. You can now delete the old table and rename the new one.

View all Access hints and tips