Quarterley Date Range display

[Brian]

Is there a way to get a quarterly date range display in a text box on a
summary report?

For example:

Quarterly Date Range Value Field 2 Field 3
Field 4
Record1 Jan - Mar
Record2 Apr - Jun
Record3 Jul - Sep
Record4 Oct - Dec
Record5 2006

 

[Duane Hookom]

Probably. Can you share anything about your record source of your report?

 

[Brian]

The record source of this report is a query. The query consists of a
quarterly count of hip procedures in our Operating rooms. I am trying to
manipulate a date field of known as Surgery date within the report to yield
Quarterly Date Range.

Brian

 

[Duane Hookom]

I would use a user-defined function to calculate the beginning and ending
quarter dates based on a date. You could save this function in a standard
module and use it in your report like:
=GetQuarterDate([Surgery date],"b")

Function GetQuarterDate(datDate As Date, strBE As String) As Date
'this function will return the starting or ending date _
of a quarter containing datDate
' datDate is the date
' strBE is either _
"B" for Beginning or _
"E" for Ending
Dim intMth As Integer
Dim intReturnDay As Integer
Dim intYr As Integer
intYr = Year(datDate)
intMth = Month(datDate)
Select Case strBE
Case "B"
intMth = intMth - (intMth - 1) Mod 3
GetQuarterDate = DateSerial(intYr, intMth, 1)
Case "E"
intMth = intMth - (intMth - 1) Mod 3 + 2
GetQuarterDate = DateSerial(intYr, intMth + 1, 0)
End Select

End Function

 

[Brian]

Thanks Duane,

I used your code except I made one change, I used the following in my
control source:

=GetQuarterDate([Surg Date],"b") & "-" & GetQuarterDate([Surg Date],"e")

This way I got the full date range. Can I convert this date format to long
date format?

Brian.

I would use a user-defined function to calculate the beginning and ending
quarter dates based on a date. You could save this function in a standard
module and use it in your report like:
=GetQuarterDate([Surgery date],"b")

Function GetQuarterDate(datDate As Date, strBE As String) As Date
'this function will return the starting or ending date _
of a quarter containing datDate
' datDate is the date
' strBE is either _
"B" for Beginning or _
"E" for Ending
Dim intMth As Integer
Dim intReturnDay As Integer
Dim intYr As Integer
intYr = Year(datDate)
intMth = Month(datDate)
Select Case strBE
Case "B"
intMth = intMth - (intMth - 1) Mod 3
GetQuarterDate = DateSerial(intYr, intMth, 1)
Case "E"
intMth = intMth - (intMth - 1) Mod 3 + 2
GetQuarterDate = DateSerial(intYr, intMth + 1, 0)
End Select

End Function

--
Duane Hookom
MS Access MVP

The record source of this report is a query. The query consists of a
quarterly count of hip procedures in our Operating rooms. I am trying to
manipulate a date field of known as Surgery date within the report to
yield
Quarterly Date Range.

Brian

 

[Duane Hookom]

You should be able to do this the same as you would format any date:

=Format(GetQuarterDate([Surg Date],"b"),"Long date") & "-" &
Format(GetQuarterDate([Surg Date],"e"),"Long Date")


--
Duane Hookom
MS Access MVP

Thanks Duane,

I used your code except I made one change, I used the following in my
control source:

=GetQuarterDate([Surg Date],"b") & "-" & GetQuarterDate([Surg Date],"e")

This way I got the full date range. Can I convert this date format to
long
date format?

Brian.

I would use a user-defined function to calculate the beginning and ending
quarter dates based on a date. You could save this function in a standard
module and use it in your report like:
=GetQuarterDate([Surgery date],"b")

Function GetQuarterDate(datDate As Date, strBE As String) As Date
'this function will return the starting or ending date _
of a quarter containing datDate
' datDate is the date
' strBE is either _
"B" for Beginning or _
"E" for Ending
Dim intMth As Integer
Dim intReturnDay As Integer
Dim intYr As Integer
intYr = Year(datDate)
intMth = Month(datDate)
Select Case strBE
Case "B"
intMth = intMth - (intMth - 1) Mod 3
GetQuarterDate = DateSerial(intYr, intMth, 1)
Case "E"
intMth = intMth - (intMth - 1) Mod 3 + 2
GetQuarterDate = DateSerial(intYr, intMth + 1, 0)
End Select

End Function

--
Duane Hookom
MS Access MVP

The record source of this report is a query. The query consists of a
quarterly count of hip procedures in our Operating rooms. I am trying
to
manipulate a date field of known as Surgery date within the report to
yield
Quarterly Date Range.

Brian

:

Probably. Can you share anything about your record source of your
report?

--
Duane Hookom
MS Access MVP

Is there a way to get a quarterly date range display in a text box
on
a
summary report?

For example:

Quarterly Date Range Value Field 2
Field
3
Field 4
Record1 Jan - Mar
Record2 Apr - Jun
Record3 Jul - Sep
Record4 Oct - Dec
Record5 2006