How to Auto Display Yearly Contract Expiration

[chaingangextreme]

Hi!

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.

Chain

[TheSmileyCoder]

You can use the DateDiff and DataAdd functions.

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

Add 2 more textboxes lets call them tb_Expire and tb_DaysLeft.

In tb_Expire's CONTROLSOURCE you write:

=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:

=IIF(tb_HiredDate is Null;;dateadd("yyyy";1;tb_HiredDate))

Similarly for tb_DaysLeft:

=IIF(tb_HiredDate is Null;;datediff("d";tb_Expire;Date()))

[chaingangextreme]

Hello,

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.

[zmbd]

go here and take a look at the syntax for dateadd:
http://office.microsoft.com/en-us/ac...001228810.aspx

go here and take a look at the syntax for datediff:
http://office.microsoft.com/en-us/ac...001228811.aspx

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

-z

[chaingangextreme]

thanks for the reply, i will read and try the link and we'll get back if there are more clarifications.

[chaingangextreme]

Hi!
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.

Thanks.

[zmbd]

chaingangeextreme:

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

-z

[chaingangextreme]

Hi!
Again just can't get through with the same error. I attached the error in word form.

Thanks.

[TheSmileyCoder]

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:

Public Function CalculateNextExpiry(InputDate As Variant) As Variant
   'I added this to elegantly handle the case of a new record (in which hireddate is null)
      If Nz(InputDate, -1) = -1 Then Exit Function
 
   'Now calculate next hireddate
      Dim DeltaYear As Integer
      DeltaYear = Year(Date) - Year(InputDate)
 
      CalculateNextExpiry = DateAdd("yyyy", DeltaYear, InputDate)
 
   'Has the date passed this year?
      If CalculateNextExpiry < Date Then
         'Add another year
         CalculateNextExpiry = DateAdd("yyyy", 1, CalculateNextExpiry)
      End If
End Function

You can now call the customfunction where you need it, for example you could set the controlsource of your textbox to be:
=CalculateNextExpiry(tb_HiredDate)

For more information on using custom functions see:
http://bytes.com/topic/access/insigh...-access-2003-a