Results 1 to 5 of 5
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Date filtering: between x months ago and last day of previous month

    I'm working with a data set that is being added to daily, but is typically analyzed month by month. I currently have set up a date filter that I thought was working to give me records between 'x' months ago to the current month:



    Code:
    >=DateAdd("m",-returnDateRange(),Date())
    (returnDateRange is just a function to pass a global variable from a form that displays charts. It is the 'x'.)

    However, I then realized that what this does is take the current date minus x months; so, today, if x=2, that would be March 11 through May 11, rather than betwee March 1st and May 1st.

    Could it be something like Between DateAdd("m",-returnDateRange(),Date()) and DateSerial(Year(Date()), Month(Date()), 1)?

    I think that would make sense but I want to sanity check since I was wrong last time.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I use a form with 2 textboxes: txtStartDate , txtEndDate
    to set any range, but it also has an option to do a 'month' but the code is:

    txtEndDate = DateAdd("d", -1, DateAdd("m", 1,txtStartDate))

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Pawtang - You could easily test each part of your expression in the immediate window.
    For example, using the first part of your posted expression where I substitute the function for a value:
    ?DateAdd("m",-2,Date())
    3/11/21
    So that's not right.

    And the suggested expression:
    ?DateAdd("d", -1, DateAdd("m", 1,txtStartDate))
    1/29/1900

    and what should work:
    DateSerial(Year(Date), Month(Date) - 2, 1)

    EDIT - BTW, your second half doesn't give you the last day of the previous month either, which is mentioned in your post title. For that you need to subtract 1.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Quote Originally Posted by Micron View Post
    @Pawtang - You could easily test each part of your expression in the immediate window.
    For example, using the first part of your posted expression where I substitute the function for a value:
    ?DateAdd("m",-2,Date())
    3/11/21
    So that's not right.

    And the suggested expression:
    ?DateAdd("d", -1, DateAdd("m", 1,txtStartDate))
    1/29/1900

    and what should work:
    DateSerial(Year(Date), Month(Date) - 2, 1)

    EDIT - BTW, your second half doesn't give you the last day of the previous month either, which is mentioned in your post title. For that you need to subtract 1.
    Looks like I got halfway there. The DateSerial expression was a good step, but should also be applied to the first portion of the date range. Ultimately:

    Between DateSerial(Year(Date()), Month(Date())-returnDateRange(), 1) and DateSerial(Year(Date()), Month(Date()), 0)

    Using 0 for the day does work to give you last day of previous month. Thanks for the Immediate tip, I never knew how to use it before.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Glad I could help.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 05-03-2017, 09:48 AM
  2. Replies: 6
    Last Post: 08-17-2016, 07:13 AM
  3. Replies: 3
    Last Post: 05-10-2016, 11:51 AM
  4. Replies: 1
    Last Post: 10-22-2014, 11:20 AM
  5. Replies: 1
    Last Post: 09-06-2014, 01:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums