Calculated field
in [Queries]
|
Prev: Append only new records/entire record makes unique identifier
Next: Query results where gaps exist Min & Max do not work
From: LoriP on 12 May 2010 12:19 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
From: Chris on 12 May 2010 12:31 Hi, Go to online help and look for DATESERIAL function. I think that should help you out. "LoriP" wrote: > 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
From: Bob Barrows on 12 May 2010 12:45 LoriP wrote: > 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 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? Probably, but you'll need to tell us what results you want if the first visit is 1/31/2011 or 5/31/2010 -- HTH, Bob Barrows
From: KARL DEWEY on 12 May 2010 12:53 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" wrote: > 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
From: LoriP on 12 May 2010 13:02
Thanks Karl, that was exactly the simple answer I was looking for and it works. "KARL DEWEY" wrote: > 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" wrote: > > > 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 |