Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003

    Question Unanswered: Querying based on two dates

    Here is the scenario and for the life of me, I can't figure it out:

    I have a report that I need to generate to determine the number of days stayed in a room.

    I have a form that has two unbound text boxes (Start Date and End Date) that the user will fill in. The query I run pulls filtered data from the table with the Check-in Date field having the criteria of "Between [StartDate] and [EndDate]. The criteria does reference the right controls on the form. The proper syntax has been left out for simplicity.

    Use this data as an example:
    Record 1: Name: John Doe, Check-in Date: 1-5-03, Check-out Date: 1-10-03

    Record 2: Name: Jane Doe, Check-in Date: 1-10-03, Check-out Date: 3-10-03

    Record 3: Name: Jim Doe, Check-in Date: 2-12-03, Check-out Date: 2-14-03

    Here is my dilema. I can calculate the total number of days stayed perfectly by subtracting the Check-In Date from the Check-out Date. What I need to be able to do is calculate the number of days stayed for each month prior if their stay overlaps a month or more. If I run a report to filter from 1-1-03 to 1-31-03, Record 1 and 2 are returned with the proper length of stay calculated. If I run a report from 2-1-03 to 2-28-03, only record 3 is returned. What I need it to do is return Record 2 as well in order to show that the person stayed 28 days in February and 21 days in January. I know why Record 2 isn't being returned. It's because the Check-in Date isn't between the Start Date and End Date criteria from the form.

    My question is, how do I create the query or code for the report so that it knows to look for that second record and calculate the days stayed properly for both January and February?


  2. #2
    Join Date
    Jan 2003
    Use the DateDiff command to filter

Posting Permissions

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