Quarter Dates

[Guest]

Hi all

I need a routine running once a quarter. I want the default [Quarterdate] to
be suggested to the user to be the first of the month of the next quarter(if
this makes sense?). Quarters are 01 Jan - 31 March etc.

I then want to append to a table all dates in this quarter for each person
in my Specialists Table who have Plan = True (storing field SpecID and
PlanDate in Table QuarterlyPlan). Would I use
EndQuarter=DatAdd([QuarterDate,"m",3).

=QuarterDate and < EndQuarter

Then loop through code creating dates until date is less than EndQuarter. Or
use code to work out how many days between the two dates then use this in a
for to loop? I am not sure how to do it for each Person's records, but I
could maybe achieve this with queries once the quarter dates had been created.

Thanks in advance for any help.
Sue

 

[John Spencer]

Start Date of quarter
DateSerial(Year(Date()),1+(DatePart("q",Date()))*3,1)

End Date of next quarter
DateSerial(Year(Date()),4+(DatePart("q",Date()))*3,0)

As for the rest of your problem, I do not understand what you are
attempting.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi all

I need a routine running once a quarter. I want the default [Quarterdate]
to
be suggested to the user to be the first of the month of the next
quarter(if
this makes sense?). Quarters are 01 Jan - 31 March etc.

I then want to append to a table all dates in this quarter for each person
in my Specialists Table who have Plan = True (storing field SpecID and
PlanDate in Table QuarterlyPlan). Would I use
EndQuarter=DatAdd([QuarterDate,"m",3).

=QuarterDate and < EndQuarter

Then loop through code creating dates until date is less than EndQuarter.
Or
use code to work out how many days between the two dates then use this in
a
for to loop? I am not sure how to do it for each Person's records, but I
could maybe achieve this with queries once the quarter dates had been
created.

Thanks in advance for any help.
Sue

 

[Guest]

Thanks for the quarter syntax.

Sorry, I'll try to explain better. I want to create records in a table for
dates in the next quarter which I will send to each member of staff to fill
out the rest of the details (their activities).

As an example I did this earlier so I could start working on the next bit
with some test data:

Dim dt As Date
Dim rs As DAO.Recordset
Dim i As Integer

Set rs = DBEngine(0)(0).OpenRecordset("QuarterlyPlan")
dt = #1/1/2007#

With rs
For i = 0 To 90
.AddNew
!ReviewDate = DateAdd("d", i, dt)
!SpecID = 1
.Update
Next
End With
rs.Close
Set rs = Nothing

Which created me Quarter 1 dates for 2006 for the one person (whereas I need
it for all staff who will have Plan = True in Specialists Table. I also want
the code to skip Saturday and Sunday dates as these are not worked. The 0 to
90 will not always stop at the right date either since there are different no
of days per month, hence I wanted the formula for calculating the end of
quarter period.

Thanks
Sue

Start Date of quarter
DateSerial(Year(Date()),1+(DatePart("q",Date()))*3,1)

End Date of next quarter
DateSerial(Year(Date()),4+(DatePart("q",Date()))*3,0)

As for the rest of your problem, I do not understand what you are
attempting.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi all

I need a routine running once a quarter. I want the default [Quarterdate]
to
be suggested to the user to be the first of the month of the next
quarter(if
this makes sense?). Quarters are 01 Jan - 31 March etc.

I then want to append to a table all dates in this quarter for each person
in my Specialists Table who have Plan = True (storing field SpecID and
PlanDate in Table QuarterlyPlan). Would I use
EndQuarter=DatAdd([QuarterDate,"m",3).

=QuarterDate and < EndQuarter

Then loop through code creating dates until date is less than EndQuarter.
Or
use code to work out how many days between the two dates then use this in
a
for to loop? I am not sure how to do it for each Person's records, but I
could maybe achieve this with queries once the quarter dates had been
created.

Thanks in advance for any help.
Sue