Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2003
    Posts
    5

    Unanswered: Date range problem - query criteria

    I can't seem to figure out how to get an inclusive range of dates in a query when the date field is in the "date/time" format (DD/MM/YYYY TT:TT AM). When I create a query that has a criteria of "Between [StartDate] And [EndDate]" It will not include the date entered for EndDate. For example, when I run the query and enter "10/20/03" for StartDate and "10/20/03" for EndDate, I get no records returned. However, if I enter "10/20/03 00:00 AM" for StartDate and "10/20/03 23:59 PM" for EndDate, I get all the records for 10/20/03 returned.
    My question is:
    How can I get the query to return an inclusive range of dates without having to type in a range of times as well?
    Thanks.

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    givtu:
    I thing that in the date field of the query see proprieties
    and selecc short date.
    Saludos
    Norberto

  3. #3
    Join Date
    Nov 2003
    Posts
    5
    Didn't work. Thanks though.

  4. #4
    Join Date
    Aug 2002
    Location
    Florida
    Posts
    2
    Try formating the date field range in the query. Right click on the Field you are querying and format it using shortdate. 11/05/2003. Then your request as between #11/05/2003# and #11/05/2003#. If I can suggest if your end date and started are not the same, you are requesting all records that meet both criterias. Search by one criteria and then check your results.

  5. #5
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    465
    jemaccou have reason in the criteria the dat date must be # / / # and not " / / ".
    Saludos
    Norberto

  6. #6
    Join Date
    Nov 2003
    Posts
    5
    Thanks. I tried that but still can't get the results to be inclusive unless I include the time along with the date. I formatted the date field as short date. I even formatted the field in the table the query is pulling the data from with short date. It still won't include the EndDate in the results. Frustrating!

  7. #7
    Join Date
    Aug 2002
    Location
    Florida
    Posts
    2
    are your date fields defined as datetime.

  8. #8
    Join Date
    Nov 2003
    Posts
    5
    Yes, the date field is defined as date/time.
    I wonder if there's a way for the query to add one day to the EndDate value that the user keys in...

  9. #9
    Join Date
    Nov 2003
    Posts
    5
    Figured it out.
    After formatting the fields as suggested above, I split up the criteria in the query and put them on separate lines:

    Criteria: >=[StartDate]
    or: <=[EndDate]

    This worked. That's weird since originally I had the criteria:

    Criteria: Between [StartDate] And [EndDate]

    AND I ALSO TRIED

    Criteria: >=[StartDate] And <=[EndDate]

    Neither of these worked. Huh.

    Thanks for the help!

  10. #10
    Join Date
    Nov 2003
    Posts
    300
    That is weird too,

    I tried --- Criteria: >=[StartDate] And <=[EndDate]
    on one of the Queiries and it worked !!

Posting Permissions

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