adding subsequent Quarters to start of year

  • Thread starter Thread starter nerb61 via AccessMonster.com
  • Start date Start date
N

nerb61 via AccessMonster.com

I'm having trouble getting this code to work in my course selection db - I
need to have a selection criteria for each quarter starting with the
beginning of each year. Any help would be appreciated.

Public Function CalcStartEnd(intYear As Integer, intSession As Integer, _
datStart As Date, datEnd As Date) As Integer
Dim datYearStart As Date
' Either year or Q has updated
' Calculate default start and end days
' Make sure we have two values
If IsNothing(intYear) Or _
IsNothing(intSession) Then
CalcStartEnd = False
Exit Function
End If
' Set up for a successful return
CalcStartEnd = True
' Calculate the first day of the year
datYearStart = DateSerial(intYear, 1, 1)
' Set days based on quarter
Select Case intSession
' Q1
Case 1
' Q1 Jan - March
datStart = datYearStart
' .. and ends March 31
datEnd = datYearStart + DateAdd(qq, 1, datYearStart)
' Q2 short semester
Case 2
' Q2 April - June
datStart = DateAdd(qq, 1, datYearStart)
' .. and ends June 30
datEnd = DateAdd(qq, 2, datYearStart)
' Q3 semester
Case 3
' Q3 July - September
datStart = DateAdd(qq, 2, datYearStart)
' .. and ends September 30
datEnd = DateAdd(qq, 3, datYearStart)
Case 4
' Q4 October - December
datStart = DateAdd(qq, 3, datYearStart)
' .. and ends on December 31
datEnd = DateAdd(qq, 4, datYearStart)
Case Else
' Not a valid Q
CalcStartEnd = False
End Select
End Function
 
I think this is what you are trying to do:

Public Function CalcStartEnd(varYear As Variant, intQuarter As Integer, _
dtStart As Date, dtEnd As Date) As Boolean
If IsNumeric(varYear) And intQuarter > 0 And intQuarter <= 4) Then
dtStart = DateSerial(varYear, 3 * (intQuarter - 1), 1)
dtEnd = DateAdd("m", 3, dtStart) - 1
CalcStartEnd = True
End If
End Function

Presumably you need to be able to pass in any expression (perhaps even Null)
for the year, so it must be declared as a Variant. Instead of testing for
all the wrong types, just test if it is numeric.
 
I actually need the four quarters to return as choices, i.e if I select Q3,
the date fields will auto populate with the third quarter dates, etc.

Allen said:
I think this is what you are trying to do:

Public Function CalcStartEnd(varYear As Variant, intQuarter As Integer, _
dtStart As Date, dtEnd As Date) As Boolean
If IsNumeric(varYear) And intQuarter > 0 And intQuarter <= 4) Then
dtStart = DateSerial(varYear, 3 * (intQuarter - 1), 1)
dtEnd = DateAdd("m", 3, dtStart) - 1
CalcStartEnd = True
End If
End Function

Presumably you need to be able to pass in any expression (perhaps even Null)
for the year, so it must be declared as a Variant. Instead of testing for
all the wrong types, just test if it is numeric.
I'm having trouble getting this code to work in my course selection db - I
need to have a selection criteria for each quarter starting with the
Click to expand...
[quoted text clipped - 45 lines]
End Select
End Function
Click to expand...
Click to expand...
 
nerb61 via AccessMonster.com said:
I actually need the four quarters to return as choices, i.e if I select Q3,
the date fields will auto populate with the third quarter dates, etc.
Click to expand...


What date fields?

The function you posted accepts a year and a quarter, plus 2 Date type
variables that you set to the start and end of the quarter. It does not
accept any fields or controls.
 
Back
Top