1. Forum
  2. Usenet
  3. COMP.DATABASES.MS-ACCESS

  • Dynamic Date Ranges

    From musicloverlch@21:1/5 to All on Fri Sep 4 11:48:33 2020
    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)
  • From musicloverlch@21:1/5 to All on Fri Sep 4 13:09:28 2020
    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)
  • From Mike P@21:1/5 to musicloverlch on Sat Sep 5 01:35:19 2020
    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)
  • From mal.reeve@gmail.com@21:1/5 to musicloverlch on Sat Sep 5 16:57:37 2020
    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)