VBA Date Filtering Query
Results 1 to 6 of 6

Thread: VBA Date Filtering Query
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Date Filtering Query

    Good afternoon,

    When I use the following code to filter an Excel database to all rows where the date in the first field is less than or equal to the value in the named range "DeleteOldest"
    Code:
    DODCriteriaString = "<=" & Format(Sheets("Database").Range("DeleteOldest").value, "yyyy/mm/dd")
    ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:=DODCriteriaString, Operator:=xlAnd
    my procedure filters my database and returns data rows correctly

    However, when I use a variation on the above to return rows where the date in the first field is equal to the value in the named range "DeleteNewest"
    Code:
    DNDCriteriaString = "=" & Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd")
    ActiveSheet.Range("Database").AutoFilter Field:=1, Criteria1:=DNDCriteriaString, Operator:=xlAnd
    no rows are returned (although there are several)

    If I change my criteria string to
    Code:
    DNDCriteriaString = ">=" & Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd")
    it works fine, and, for the purposes of my dataset, this is also fine, as there are no rows with a date value value greater than "DeleteNewest"

    So, it works with >= and <= but not = alone.

    Can anyone suggest why this might be?

    Thanks in advance

    Pete
    Last edited by NewOrderFac33; Aug 15th, 2019 at 07:17 AM.
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  2. #2
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Date Filtering Query

    I just discovered that if I change the formatting sequence for the date thus:
    Code:
    DNDCriteriaString = "=" & Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy")
    that is from "yyyy/mm/dd" to "dd/mm/yyyy", it works with = on its own just fine.

    I have NO idea why this might be, but I'm adding it as a comment to my code for future reference before I forget!

    Pete
    Last edited by NewOrderFac33; Aug 15th, 2019 at 07:34 AM.
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  3. #3
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Date Filtering Query

    ...and for completeness, I tried both options with both formatting sequences:

    For <= and >=, you have to use "yyyy/mm/dd" - "dd/mm/yyyy" won't work
    For =, you have to use "dd/mm/yyyy" - "yyyy/mm/dd" won't work

    Bizarre, but that's VBA for you!

    Pete
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Date Filtering Query

    Glad you sorted it, but as a matter of interest, what happens if you remove the = sign for an exact match.
    Code:
    DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy")
    Last edited by Fluff; Aug 15th, 2019 at 08:06 AM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular NewOrderFac33's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,229
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Date Filtering Query

    Hi, Fluff - thanks for taking an interest

    DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "yyyy/mm/dd") doesn't work
    DNDCriteriaString = Format(Sheets("Database").Range("DeleteNewest").value, "dd/mm/yyyy") does.

    Cheers

    Pete
    When posting code in your reply, always prefix it with the HTML tag "[ code ]" (excluding the quotes and spaces) and follow it with the HTML tag "[ /code ]" (again excluding the quotes and spaces).
    This will make your code much easier to read!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,976
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: VBA Date Filtering Query

    Just one of the problems with working with Dates in VBA, for those of us who don't use the US date system.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •