Date calculations

[Guest]

Trying to get access to display the next quarter after a given date. For
example, date = 10/15/04, next quarter date to be displayed is 1/1/05 -
3/31/05. These are not calendar or fiscal quarters, only the first quarter,
second quarter, etc which occurs after date given. Date = 1/3/05 would
return value of the first quarter as 4/1/05 - 6/30/05.

Have tried various date functions, but can't get it.

Any help is appreciated.

 

[Allen Browne]

Next quarter starts:
DateSerial(Year(Date), 3 * (DatePart("q", Date) -1) + 4, 1)

To get the end, DateAdd() 3 months, and subtract 1.

 

[John Vinson]

Trying to get access to display the next quarter after a given date. For
example, date = 10/15/04, next quarter date to be displayed is 1/1/05 -
3/31/05. These are not calendar or fiscal quarters, only the first quarter,
second quarter, etc which occurs after date given. Date = 1/3/05 would
return value of the first quarter as 4/1/05 - 6/30/05.

Have tried various date functions, but can't get it.

Any help is appreciated.
Try:

= DateSerial(Year([GDate]), 3*(Month([GDate]) - 1) \ 3 + 3, 1) AND < DateSerial(Year([GDate]), 3*(Month([GDate]) - 1) \ 3 + 6, 1)

Alternatively, put a calculated field in your query:

DatePart("q", [datefield])

and use a criterion of

IIF(DatePart("q", [GDate]) = 4, 1, DatePart("q", [GDate]) + 1)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Barnes]

Thanks for the reply.
Two further questions?
1. Where does the "Year" come from in the first DateSerial?
2. Is the "Date" in the DateSerial and DatePart the user-
entered date (the date that I am trying to calculate from).

 

[John Barnes]

Thanks for the reply.

Two further questions.

1. Where does the Year and Month come from in your
solution? Is that a value I type in the formula, or a
part of the user-entered value?

2. Is Gdate the user-entered value that I am trying to
calculate on?


Thanks.

-----Original Message-----

Trying to get access to display the next quarter after a given date. For
example, date = 10/15/04, next quarter date to be displayed is 1/1/05 -
3/31/05. These are not calendar or fiscal quarters, only the first quarter,
second quarter, etc which occurs after date given. Date = 1/3/05 would
return value of the first quarter as 4/1/05 - 6/30/05.

Have tried various date functions, but can't get it.

Any help is appreciated.
Try:

= DateSerial(Year([GDate]), 3*(Month([GDate]) - 1) \ 3 +

3, 1) AND < DateSerial(Year([GDate]), 3*(Month([GDate]) -
1) \ 3 + 6, 1)

Alternatively, put a calculated field in your query:

DatePart("q", [datefield])

and use a criterion of

IIF(DatePart("q", [GDate]) = 4, 1, DatePart("q", [GDate]) + 1)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.

 

[Douglas J. Steele]

John's probably not up yet, so let me answer in his stead.

Year and Month are VBA functions that, respectively, extract the Year and
Month components from a given date.

GDate is the value on which you're trying to calculate.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks for the reply.

Two further questions.

1. Where does the Year and Month come from in your
solution? Is that a value I type in the formula, or a
part of the user-entered value?

2. Is Gdate the user-entered value that I am trying to
calculate on?


Thanks.

-----Original Message-----

Trying to get access to display the next quarter after a given date. For
example, date = 10/15/04, next quarter date to be displayed is 1/1/05 -
3/31/05. These are not calendar or fiscal quarters, only the first quarter,
second quarter, etc which occurs after date given. Date = 1/3/05 would
return value of the first quarter as 4/1/05 - 6/30/05.

Have tried various date functions, but can't get it.

Any help is appreciated.
Try:

= DateSerial(Year([GDate]), 3*(Month([GDate]) - 1) \ 3 +

3, 1) AND < DateSerial(Year([GDate]), 3*(Month([GDate]) -
1) \ 3 + 6, 1)

Alternatively, put a calculated field in your query:

DatePart("q", [datefield])

and use a criterion of

IIF(DatePart("q", [GDate]) = 4, 1, DatePart("q", [GDate]) + 1)


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
.