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

How to Auto Display Yearly Contract Expiration

P: 9

I would like to ask help on my form here is the question;

I have a textbox bound to Hired Date, how can I display automatically the Contract Expiration by yearly and the Days remaining before the expiration.
For example:
When I entered the Hired Date as 01/31/2002, the Contract Expiration will be displayed automatically on the future date as 01/31/2013 and the remaining days before expiration:___

Thanks for your help in Advance.

Jun 11 '12 #1
Share this Question
Share on Google+
8 Replies

Expert Mod 100+
P: 2,321
You can use the DateDiff and DataAdd functions.

Lets say your textbox bound to the Hired date is called:

Add 2 more textboxes lets call them tb_Expire and tb_DaysLeft.

In tb_Expire's CONTROLSOURCE you write:
Expand|Select|Wrap|Line Numbers
  1. =dateadd("yyyy";1;tb_HiredDate)
If you do not always have a value for tb_HiredDate I think it may give an error so we can modify the above to look like:
Expand|Select|Wrap|Line Numbers
  1. =IIF(tb_HiredDate is Null;;dateadd("yyyy";1;tb_HiredDate))
Similarly for tb_DaysLeft:
Expand|Select|Wrap|Line Numbers
  1. =IIF(tb_HiredDate is Null;;datediff("d";tb_Expire;Date()))
Jun 11 '12 #2

P: 9

Thanks for the reply sir, however, I have tried the following condition and I got some error with the expression saying that "the expression you entered contained invalid syntax". I changed the semicolon to comma between "1" and I got answer incremented by one year only. What if the data input is 01/31/2002, how will the future expiration by yearly be auto display lets say on 01/31/2013 for a yearly expiration and also by remaining days?

Sorry I am only a begginer on this but your help sure will enhance my skills.
Jun 11 '12 #3

Expert Mod 5K+
P: 5,397
go here and take a look at the syntax for dateadd:

go here and take a look at the syntax for datediff:

Sometimes the MS explanations are not very clear so don't worry if you need to ask for clarification on a point or two.

Jun 11 '12 #4

P: 9
thanks for the reply, i will read and try the link and we'll get back if there are more clarifications.
Jun 12 '12 #5

P: 9
I have tried the lesson in the link, but i can't get the desired outcome. what if the hired date has different year, I mean the data to fill in in the hired date is flexible (different dates) how can i get the future expiration exactly in the present year? Likewise on the remaining days.

Jun 12 '12 #6

Expert Mod 5K+
P: 5,397

The code that TheSmileyCoder posted in #2 should handle what you're asking... a simple modification based upon those examples and the the syntax information from #4 should have worked.

Please post the code you've tried so far... more than likely it's something small like a missing comma etc...

Jun 12 '12 #7

P: 9
Again just can't get through with the same error. I attached the error in word form.

Attached Files
File Type: doc Date error.doc (169.5 KB, 217 views)
Jun 13 '12 #8

Expert Mod 100+
P: 2,321
Now its a bit more complicated so I would write a custom function in VBA to handle it. If you add a new module to your database, and copy/paste the following code into it:
Expand|Select|Wrap|Line Numbers
  1. Public Function CalculateNextExpiry(InputDate As Variant) As Variant
  2.    'I added this to elegantly handle the case of a new record (in which hireddate is null)
  3.       If Nz(InputDate, -1) = -1 Then Exit Function
  5.    'Now calculate next hireddate
  6.       Dim DeltaYear As Integer
  7.       DeltaYear = Year(Date) - Year(InputDate)
  9.       CalculateNextExpiry = DateAdd("yyyy", DeltaYear, InputDate)
  11.    'Has the date passed this year?
  12.       If CalculateNextExpiry < Date Then
  13.          'Add another year
  14.          CalculateNextExpiry = DateAdd("yyyy", 1, CalculateNextExpiry)
  15.       End If
  16. End Function
You can now call the customfunction where you need it, for example you could set the controlsource of your textbox to be:

For more information on using custom functions see:
Jun 18 '12 #9

Post your reply

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