Increment date field

me.text0=dateadd("d",90,[Datefield])

The code cap gave would add 90 days to the date field, but what exactly do you mean by "every 90 days"? Do you need to actually do this at 90 day intervals, or do you mean something else?

Yes - I need to do at 90 day intervals - for example if the start date field was 8/1/2010 then the other field would = 1/23/2012. as of today.  Notice this date is greater than today's date. Once today's date is greater than 1/23/2012 another 90 days would be added to 1/23/2012 thus making the new date 4/22/2012

That could be at the OnOpen event of the form:

If Not IsNull(Me!OtherDate) Then
  If DateDiff("d", Me!OtherDate, Date) >= 0 Then
    Me!OtherDate = DateAdd("d", 90, Me!OtherDate)
  End If
End If

and at the AfterUpdate event of StartDate:

Me!OtherDate = DateAdd("d", 90, Me!StartDate)

/gustav

no dates are displayed in the OtherDate field

Then you need to enter a StartDate.

/gustav

Below is the code used:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me!TreatmentDate) Then
  If DateDiff("d", Me!TreatmentDate, Date) >= 0 Then
    Me!TreatmentDate = DateAdd("d", 90, Me!TreatmentDate)
  End If
End If
End Sub

Private Sub AdmitDate_AfterUpdate()
Me!TreatmentDate = DateAdd("d", 90, Me!AdmitDate)
End Sub

use the load event of the form

Private Sub Form_Load()
If Not IsNull(Me.TreatmentDate) Then
  If  Date > Me.TreatmentDate Then
    Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
  End If
End If
End Sub

Private Sub AdmitDate_AfterUpdate()
Me.TreatmentDate = DateAdd("d", 90, Me.AdmitDate)
End Sub

<want to increment an access form date field every 90 days based on another date field -- the starting point,>

This is why it is always best to provide an example of the exact input you have, and the exact output you are expecting.

Same results - I have attached a sample of the form
Sample.doc

upload a copy of the db

try this code


use the load event of the form

Private Sub Form_Load()
If Not IsNull(Me.TreatmentDate) Then
  If  Date > Me.TreatmentDate Then
    Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
  End If
else
msgbox "TreatMent Date is " & nz(Me.TreatmentDate,#1/1/1900#)
End If


post back if you see the message
End Sub

are you sure you want to check the todays date against TreatmentDate?

sorry about that error

try this code


use the load event of the form

Private Sub Form_Load()
If Not IsNull(Me.TreatmentDate) Then
  If  Date > Me.TreatmentDate Then
    Me.TreatmentDate = DateAdd("d", 90, Me.TreatmentDate)
  End If
else
msgbox "TreatMent Date is " & nz(Me.TreatmentDate,#1/1/1900#)
End If
End Sub

post back if you see the message

Click on on Add/Chng Veteran ALL to view form
Copy-of-VASH-V1.accdb

try this and post your comments



VASH-V1.accdb

It still does not populate the treatment date field on the form VASH- I get a popup message 1/1/1900 when I open the form. I'm trying to populate the treatmentdate field based on the admitdate field. The admitdate field is updated every 90 days. So for example if the admitdate is  8/1/2010 then the treatment would  = 1/23/2012. as of today.  Notice this date is greater than today's date. Once today's date is greater than 1/23/2012 another 90 days would be added to 1/23/2012 thus making the new treatmentdate 4/22/2012

sorry it should be the treatment date field is updated every 90 days

i know it will not populate..
so if the treatmentdate is null it must have a value of admitdate +90 days
 check this one


VASH-V1-2.accdb

The Kicker here is that you are not giving us the full "Context"

So do you want the second date to adjust automatically, if the first date is edited (changed to a new value)

yes to both questions

did you try the last db i uploaded?  http:#a37351785

I see that the treatmentdate field has a date 90 days from the admitdate howerver that is incorrect since today's date is greater than the the treatmentdate

ok..now looking at the records
AdmitDate
3/22/2011
4/20/2011
3/17/2011
3/29/2011


what should be the treatmentDates ?

post them here

test this one


VASH-V1-3.accdb

AdmitDate                                 Prior Treatment Dates                  TreatmentDt        8/1/2010      10/30/2010      1/28/2011      4/28/2011      7/27/2011      10/25/2011      1/23/2012

Based on the above example the treatdate field would = 1/23/2012. Notice that I progressed through severa 90 day cycles until I reached a treatmentdate that was greater than today's date.

try the last db i uploaded http:#a37351916

When I change the admitdate the treatmentdate do not change accordingly otherwise it works fine.

Also when I try to add a new record the form is in a endless loop - it locks up the database

ok i will post on another thread

When adding a new record the database goes into  an endless loop.and locks up. The code is only partially correct for the solution I'm looking for.

shieldsco,

better just delete this thread, instead of giving a C grade..