Results 1 to 8 of 8
  1. #1
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410

    13 Month Range?

    Even though my range is for 13 months with the WHERE:


    Code:
    PaymentDate = Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30),DateSerial(Year(Date())+1,5,30)))
    When I look at the details I'm only seeing 6/1/2013 to current day.

    I know there is data for May 2013.

    What is wrong with my criteria?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    I use two techniques to help me count periods.

    A table dedicated to a predetermined range of dates. This table has columns of integer type dedicate to numbers that represent, weekday, month, and year. A column for each.

    The second technique is to employ alias' in queries. An alias for weekday, month, and year. All of the alias' are integers.

  3. #3
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Ok, but why would this not result in May data?

    Code:
    Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30),DateSerial(Year(Date())+1,5,30)))

  4. #4
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    The WHERE translate to "5/1/2013" to "5/30/2014".

    Between IIf(Month(Date())<=5,DateSerial(Year(Date())-1,5,1),DateSerial(Year(Date()),5,1)) And IIf(Month(Date())<=5,DateSerial(Year(Date()),5,30) ,DateSerial(Year(Date())+1,5,30))

    So, if there is data with "PaymentDate" (Date/Time type), it should pick up from "5/1/2013" to "5/30/2014".

    Can you upload the small sample of your database for close look?

  5. #5
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Exactly!

    But for some reason it's showing only from 6/1/13 through May 2014 when there is data for May 2013.

    Will do.

    thank you

  6. #6
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Here it is.

    There is one record from May 2013.
    It should show BUT it does not.

    Mbrshp.zip

  7. #7
    lfpm062010 is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Location
    US
    Posts
    415
    It looks like the "EndYear >=Year(Date())" remove all your entry prior to 7/3/2013.

  8. #8
    aellistechsupport is offline Competent Performer
    Windows 7 32bit Access 2003
    Join Date
    Apr 2014
    Posts
    410
    Shoot!

    DOH!

    Well, yes that makes sense since the May 2013 record also has an EndDate of Sep 2013.

    Sorry for the trouble!

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

Similar Threads

  1. Replies: 43
    Last Post: 08-06-2014, 08:21 PM
  2. Replies: 6
    Last Post: 05-05-2014, 10:51 PM
  3. Replies: 4
    Last Post: 05-26-2013, 03:28 PM
  4. Date Range Query Only Returns Month and Day
    By hammer187 in forum Queries
    Replies: 5
    Last Post: 09-18-2012, 11:25 AM
  5. Replies: 4
    Last Post: 05-26-2012, 09:29 AM

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