Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Between' operator (AXP (2002) SP-1)

    is there a way to force the "between" logical operator to evaluate inclusive? I am evaluating data for "last month". When I write a date criteria like: "between #9/1/2002# and #9/30/2002#", it only produces data literally "between" those dates, leaving out the 1st and last dates of the month (which I guess is what I should be expected).

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: 'Between' operator (AXP (2002) SP-1)

    That's strange, because Between ... And ... is supposed to be inclusive. One thing to check: #9/30/2002# is interpreted as midnight at the beginning of September 30. If the date field in your table has been filled with date-and-time values, for instance by the function Now(), then records with a date 9/30/2002 but with a time after midnight will not meet the criteria. If the date field has been formatted to show only a date, you wouldn't see the time part. Records with a date 9/1/2002 should still be included, however.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Between' operator (AXP (2002) SP-1)

    I believe that the field has been formatted with both date and time, which would account for no data on the last day of the month. I guess I have to look for "between 9/1/2002 and 10/1/2002" in order to get at the last day of the month.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2010
    Location
    Denver, CO
    Posts
    191
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Between' operator (AXP (2002) SP-1)

    I believe that the field has been formatted with both date and time, which would account for no data on the last day of the month. I guess I have to look for "between 9/1/2002 and 10/1/2002" in order to get at the last day of the month. From what you indicated, this would not include data from 10/1/2002, as long as the field was actually formated with date and time.

    Thanks for your help!

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Mishawaka, Indiana, USA
    Posts
    145
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Between' operator (AXP (2002) SP-1)

    Why don't you format your date field as either Short, Medium, or Long date in the table? These formats do not save the time and your between expression will work fine.

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Between' operator (AXP (2002) SP-1)

    You still need to be careful not to use Now() to initialize values or the time of day (in addition to the date) will still be stored even though it won't be displayed.

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: 'Between' operator (AXP (2002) SP-1)

    Actually, it would include midnight of the penultimate date, so between may not be the best way if you actually have records that are timestamped for midnight. We use date/time fields a lot and don't use between for that reason.
    Charlotte

Posting Permissions

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