I'm stumped.

I have a form with a Quarter End Date. Let's say it's 9/30/2020. I need to give the user the ability to click on a button and it will count all the records from a transaction table that occurred between 7/1/2020 and 9/30/2020 and return the total
number of transactions. My problem is that it has to be dynamic. If the quarter-end is 12/31/2020 then it will count between 10/1/2020 and 12/31/2020. How in the world do I achieve that in VBA? I've hit a wall.

Thanks,
Laura

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Well, I wish it was more elegant. This is kinda clunky. I'm still open to ideas.

If Month(Me.QuarterEnd) = 3 Then
intMonthStart = 1
intYearStart = Year(Me.QuarterEnd)
dteSearchStart = DateValue(intMonthStart & "/1/" & intYearStart)

intMonthEnd = 3
intYearEnd = Year(Me.QuarterEnd)
dteSearchEnd = DateValue(intMonthEnd & "/31/" & intYearEnd)
End If

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, September 4, 2020 at 9:09:33 PM UTC+1, musicloverlch wrote:

Well, I wish it was more elegant. This is kinda clunky. I'm still open to ideas.

If Month(Me.QuarterEnd) = 3 Then
intMonthStart = 1
intYearStart = Year(Me.QuarterEnd)
dteSearchStart = DateValue(intMonthStart & "/1/" & intYearStart)

intMonthEnd = 3
intYearEnd = Year(Me.QuarterEnd)
dteSearchEnd = DateValue(intMonthEnd & "/31/" & intYearEnd)
End If

Hi Musicloverich,

If the value of (Me.QuarterEnd) is a reliable Quarter End Date, then the Quarter Start Date will be
DateSerial(Year(Me.QuarterEnd),Month(Me.QuarterEnd)-2,1).

Mike P.
5/9/20

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hello
Checkout the DatePart function, which will return the quarter of any given date.
https://support.microsoft.com/en-us/office/datepart-function-26868a79-5505-4e5a-8905-6001372223fa

and you can use it in a query, or in vba.
This ugly example below would grab all records for the current financial year

SELECT tblImportantInfo.tblImportantInfoDATEField
FROM tblImportantInfo
WHERE (((DatePart("q",[tblImportantInfoDATEField]))=DatePart("q",Date())) AND ((Year([tblImportantInfoDATEField]))=Year(Date())));

If you form button is filtering the list to only show the current Qtr, you can just have a form field of
=Count(primarykey)

This page has some other examples around Quarters. http://codevba.com/office/quarter_date_functions.htm#.X1Qls1Uzbcc

HTH
Mal.


On Saturday, September 5, 2020 at 6:09:33 AM UTC+10, musicloverlch wrote:

Well, I wish it was more elegant. This is kinda clunky. I'm still open to ideas.

If Month(Me.QuarterEnd) = 3 Then
intMonthStart = 1
intYearStart = Year(Me.QuarterEnd)
dteSearchStart = DateValue(intMonthStart & "/1/" & intYearStart)

intMonthEnd = 3
intYearEnd = Year(Me.QuarterEnd)
dteSearchEnd = DateValue(intMonthEnd & "/31/" & intYearEnd)
End If

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)