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

    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?

    Regards,
    Jon

  2. #2
    Join Date
    Jan 2003
    Posts
    15
    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
  •