Results 1 to 2 of 2
  1. #1
    WeekendCoder is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Location
    GB
    Posts
    14

    Cool Dynamic Date Range

    Purpose:
    Create a date range among the three most recent years from the beginning of each year to the current month/day for each year to be used in a query.

    Ex:
    1/1/2014-10/16/2014


    1/1/2015-10/16/2015
    1/1/2016-10/16/2016

    Code:
    ("Between #" & CDate("1/1/" & Year(Date())) & "# And #" & Date() & "#") Or ("Between #" & CDate("1/1/" & Year(DateAdd("yyyy",-1,Date()))) & "# And #" & DateAdd("yyyy",-1,Date()) & "#") Or ("Between #" & CDate("1/1/" & Year(DateAdd("yyyy",-2,Date()))) & "# And #" & DateAdd("yyyy",-2,Date()) & "#")
    Issue:
    String doesn't convert to meaningful date range. Even a simpler version doesn't yield any results.

    Code:
    "(Between #" & CDate("1/1/" & Year(Date())) & "# And #" & Date() & "#)"
    The second line of code does translate into a date range using VBA Immediate window.

    Code:
    (Between #1/1/2016# And #10/16/2016#)
    However, when inserted into a query criteria the following is returned:
    "The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
    I doubt the code is too complicated so therefor it must be typed incorrectly. I tried to find a similar posting but could not.

    Any suggestions?
    Thank you.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    In a query, try this type of thing:

    Between DateSerial(Year(Date()),1,1) And Date()
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 4
    Last Post: 11-20-2018, 11:57 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. Dynamic Date Range?
    By aellistechsupport in forum Programming
    Replies: 7
    Last Post: 04-29-2014, 09:25 AM
  4. VB Dynamic Range Sorting
    By samthomasny in forum Programming
    Replies: 2
    Last Post: 03-02-2013, 05:39 PM
  5. DSum with Dynamic Range
    By Kimbertha in forum Queries
    Replies: 15
    Last Post: 11-05-2010, 11:47 AM

Tags for this Thread

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