Automatic date change 1

[smalty]

I currently have four date fields: AgreementDate, RevCompDt, RevDueDt and RevByDt. The AgreementDate and RevCompDt are manual entry fields and RevDueDt and RevByDt are calculated.

The formula for each is:RevDueDt = IIf(IsNull([RevCompDt]),[AgreementDate]+335,[RevCompDt]+335)
RevByDt = [RevDueDt]+60

So for example if the AgreementDate is 01/06/2015 and there is no RevCompDt the RevDueDt will be 01/06/2015 + 335 days and the RevByDt will be 01/06/2015 + 395 days or (RevDueDt + 60 days). The following year the RevCompDt will be entered and the formulas will use that date to calculate the next RevDueDt and RevByDt.

I want to change this so that the RevDueDt and RevByDt are always, in this example, the 01/06 - and + 30 days the next year ie: when 01/06/2016 + 30 days has gone the ReveDueDt and RevByDt automatically changes to 01/06/2016 - 30 days and 01/06/2016 + 30 days.

Hope the explanation and example is clear enough. Any help much appreciated.

Regards
Smalty

 

[Andrzejek]

>The AgreementDate and RevCompDt are manual entry fields and RevDueDt and RevByDt are calculated.

Don't keep the calculated data in the table.
Create a query to calculate the RevDueDt and RevByDt fields.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[smalty]

Thanks for the reply.
I understand what you say about the query but I still have the same problem. How do I get them to calculate the same DAY/MONTH every subsequent year???

 

[Andrzejek]

Could you provide some sample data of the dates you do have (black) and the calculated dates (blue) that you would like to have?

[pre]
AgreementDate RevCompDt [blue]RevDueDt RevByDt[/blue]
1/1/2015 2/2/2015[blue] ??? ???[/blue]
10/10/2015 12/12/2015[blue] ??? ???[/blue]
5/5/2015 [blue] ??? ???[/blue]
9/9/2015[blue] ??? ???[/blue]


[/pre]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[smalty]

OK.....I think I may be misleading you. Let me say it another way.

Lets say I enter an Agreement Date of 01/06/2015 I want a text box (lets call it RevDt)to show 01/06/2016 and then next year on the 1st June the text box value to change to 01/06/2017 etc etc

 

[MajP]

Maybe a function like this

Public Function NextReview(OriginalDate As Date) As Date
  'Cheat here to simplify code for leap day.  Just always do the review on the 28th
  If Month(OriginalDate) = 2 And Day(OriginalDate) = 29 Then
     OriginalDate = OriginalDate - 1
  End If
  
  If DateSerial(Year(Now), Month(OriginalDate), Day(OriginalDate)) <= Date Then
     'review during this year
      NextReview = DateSerial(Year(Now) + 1, Month(OriginalDate), Day(OriginalDate))
  Else
      'review next year
      NextReview = DateSerial(Year(Now), Month(OriginalDate), Day(OriginalDate))
  End If
  
 End Function

So if the month and day have not occurred you will review this year, if they have passed you will review next year.

 

[smalty]

Hi MajP

Thanks for your reply and suggestion

Not being very familiar with public functions, I can follow the code OK and understand the logic so I am quite sure it will work but I don't understand how to pass the 'OriginalDate' to the function??

Would appreciate your further assistance please

Regards
Smalty

 

[smalty]

MajP,
Don't worry......figured it out and works good.

Thanks again
Smalty

 

[Andrzejek]

If it “works good”, show appreciation by awarding MayP a star for helpful post.
Click on “Great Post? Star it" link.


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.