Calculated field

  • Thread starter Thread starter LoriP
  • Start date Start date
L

LoriP

I need to calculate many fields based on one start date and I need the
calculations to count by month.
For example first visit is 4/25/2010
The first calculated field must be 5/25/2010 +/- 7 days
This is the first time I've had to do a calculated field where the next
visit must be the exact day of the next month and not just the generic "30
days" Is there an easy way to do this?
Thank you
Lori
 
LoriP said:
I need to calculate many fields based on one start date and I need the
calculations to count by month.
For example first visit is 4/25/2010
The first calculated field must be 5/25/2010 +/- 7 days
Click to expand...

You mean you want it to result in a range of dates? That's two fields,
not one. I think I see what you're saying: you want the result to be
obtained by changing the "4" to a "5"? Or are you saying you actually
want to add 1 month? The DateAdd() function gives you the abiility to
add specific date parts. Since months have varying lengths, you'll need
to test for unexpected results. See below
This is the first time I've had to do a calculated field where the
next visit must be the exact day of the next month and not just the
generic "30 days" Is there an easy way to do this?
Click to expand...

Probably, but you'll need to tell us what results you want if the first
visit is 1/31/2011 or 5/31/2010
 
Your calculated field cannot be be 5/25/2010 +/- 7 days. It must be a
single date.

I think you can use the DateAdd function.

Next_Appointment: DateAdd("m", 1, [start date])

If the following month has less days, February, and your start date is
greater than following month, the date will be the last day of the month.
[start date] Next_Appointment
1/31/2010 2/28/2010
 
Thanks Karl, that was exactly the simple answer I was looking for and it works.

KARL DEWEY said:
Your calculated field cannot be be 5/25/2010 +/- 7 days. It must be a
single date.

I think you can use the DateAdd function.

Next_Appointment: DateAdd("m", 1, [start date])

If the following month has less days, February, and your start date is
greater than following month, the date will be the last day of the month.
[start date] Next_Appointment
1/31/2010 2/28/2010


--
Build a little, test a little.


LoriP said:
I need to calculate many fields based on one start date and I need the
calculations to count by month.
For example first visit is 4/25/2010
The first calculated field must be 5/25/2010 +/- 7 days
This is the first time I've had to do a calculated field where the next
visit must be the exact day of the next month and not just the generic "30
days" Is there an easy way to do this?
Thank you
Lori
Click to expand...
Click to expand...
 
Thank you for the quick answer. I was just not sure what to search for and
this got me aimed in the right direction.
 
Back
Top