By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,129 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

What is a Function to Display a String if certain Date is Within Defined Date Range?

P: 10
I know I have seen this before, but I can't seem to locate the example again.

What I have is a contracts database, and I am trying to create a field that will display a notification when the current system date is within a certain number of days (i.e. 90 days) of the contract term date.

The example I have seen had a field (let's say, "Notification") in a table ("Contracts") that has its default value set to the function so that if the "Contract Term Date" field value is only 60 days away, the "Notification" field will populate with a string "Renew Contract", but if it's 100 days away, the "Notification" field is null.

Any insight would be great!
Mar 29 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 10K+
P: 12,357
It's probably a combination of the IIf() function and the DateDiff() function.
Mar 29 '12 #2

P: 759
I think is not necessary to create a field for that.
More than, I think that is WRONG to create such a field. See here why:

If you wish to see that notification in a form or a report then place an unbound text box (lets name it txtContractStatus) in the detail area of your form or report and set the ControlSource like this:
Expand|Select|Wrap|Line Numbers
  1. txtContractStatus.ControlSource = IIf(DateDiff("d", Now(), [ContractDateFieldName]) <= 90, "InTime", "OutOfTime")
Mar 30 '12 #3

Post your reply

Sign in to post your reply or Sign up for a free account.