A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Calculated field



 
 
Thread Tools Display Modes
  #1  
Old May 12th, 2010, 05:19 PM posted to microsoft.public.access.queries
LoriP
external usenet poster
 
Posts: 8
Default Calculated field

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
  #2  
Old May 12th, 2010, 05:31 PM posted to microsoft.public.access.queries
chris
external usenet poster
 
Posts: 2,039
Default Calculated field

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

  #3  
Old May 12th, 2010, 05:45 PM posted to microsoft.public.access.queries
Bob Barrows
external usenet poster
 
Posts: 475
Default Calculated field

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


  #4  
Old May 12th, 2010, 05:53 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default Calculated field

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

  #5  
Old May 12th, 2010, 06:02 PM posted to microsoft.public.access.queries
LoriP
external usenet poster
 
Posts: 8
Default Calculated field

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

  #6  
Old May 12th, 2010, 06:04 PM posted to microsoft.public.access.queries
LoriP
external usenet poster
 
Posts: 8
Default Calculated field

Thank you for the quick answer. I was just not sure what to search for and
this got me aimed in the right direction.

"Chris" wrote:

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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.