Full Version: date frequency or recurrence visits
jochy7
Hi, im a access beginner I need help, trying to shedule nurse visits every 60 days counting from the start date and assessment visits every 6 months counting from initial assessemnt date, all of them have to be in reports by months. I really appreciated that someone help me out
I'm not sure I quite understand, but if you are trying to determine the next date, 60 days later from a Nurse's Start Date, then you could do the following in a Query, for the Field. First, add the Start Date Field, then for the next field, add something like this:

This just calculates 60 days plus the Start Date Field. The DateAdd function is the key thing here.
jochy7
yes, i understand that, the problem that im facing is how can I make the supervisory date repeat itself for the next 60 days, for example if 01/01/2010 + 60 = aprox 03/01/2010, but when is going to be the next one after that and the next and so on, almost like a loop, so I can predetermine all the supervisory and reassesment dates for the future visits
Well then add another Field and then do the same thing as before, just 120, then 180, 240, etc. Is there anything wrong with that?

jochy7
thank you very much for your input, but the problem is that sometimes patients stay with us for years and such formula would have its limitation, it has to be that no matter what year of month I enter, the system should determine who's supervisory or reassessment is due, counting from the start date which does not change, but many times the end date does change because the payer extend the service to the patient
Gustav
First use DateDiff to find the decimal count of 60 days' periods from the start date to today's date.
Then round that up (using Int) to the next integer count of 60 days' periods.
Multiply this with 60 to get the count of days from the start date to "the next date".
Use this with DateAdd to find the date of "the next date":

/gustav
jochy7
hey Gustav, thank you again, I tried and it did work, how can I separate the visits by month so we can sort back and forth with monthly reports for example : visits for January , February, March and so on
Gustav
Then you will need a query or table which generates or holds the visiting dates for some period back or forth in time.
Which period and for how long?

/gustav
jochy7
hi gustav, can at least tell me how to separate by months, then i take from there. patient's information is updated constantly
Gustav
One method is to group by - in either the report or in the query which is the source of the report - by year and month:

First DatePart("yyyy", [TheNextDate]) and then DatePart("m", [TheNextDate])
Or first Year([TheNextDate]) and then Month([TheNextDate])

Or using Format:

Format([TheNextDate]), "yyyymm")

/gustav