2 different date fields with the same criteria

[Sketchin]

Hi All,

I am trying to figure out how to build my query that returns equipment reservations where "date out" OR "date In" are between 2 dates that are specified on my form using [Forms]![Myformname]![StartDate] and [Forms]![Myformname]![EndDate].

The purpose of the query is to find the amount of companies that have had a piece of equipment reserved between 2 dates, which could be a calendar year, or a fiscal year for example.

This is my current SQL statement that returns records where only DateIn falls between the criteria. My objective is to also return records where DateOut falls between 2 dates.

SELECT tblReservations.ReservationNumber, tblCompanies.CompanyName, tblLookup_Contact_types.[Contact Type], tblReservation_details.Dateout, tblReservation_details.DateIn
FROM tblLookup_Reservation_Status INNER JOIN (tblCompanies INNER JOIN ((tblLookup_Contact_types INNER JOIN tblReservations ON tblLookup_Contact_types.[Contact Code] = tblReservations.CategoryID) INNER JOIN tblReservation_details ON tblReservations.ReservationID = tblReservation_details.ReservationID) ON tblCompanies.CompanyID = tblReservations.CompanyID) ON tblLookup_Reservation_Status.ReservationStatusID = tblReservations.ReservationStatus
WHERE (((tblLookup_Contact_types.[Contact Type]) In (1,3)) AND ((tblLookup_Reservation_Status.ReservationStatusID) In (2,3)) AND ((tblReservation_details.DateIn) Between [Forms]![frmMetricsMenu]![txtDateFrom] And [Forms]![frmMetricsMenu]![txtDateTo]));

 

[Sketchin]

Figured it out. Needed to put criteria in the "or" section of the Date Out field.

 

[namliam]

Please for future reference dont just splash SQL on the forum like you did.
Do some formatting so that it is (more) readable like so-ish:

FROM tblLookup_Reservation_Status 
INNER JOIN (tblCompanies 
INNER JOIN ((tblLookup_Contact_types 
INNER JOIN tblReservations ON tblLookup_Contact_types.[Contact Code] = tblReservations.CategoryID) 
INNER JOIN tblReservation_details ON tblReservations.ReservationID = tblReservation_details.ReservationID) 
                                  ON tblCompanies.CompanyID = tblReservations.CompanyID) 
                                  ON tblLookup_Reservation_Status.ReservationStatusID = tblReservations.ReservationStatus
WHERE (((tblLookup_Contact_types.[Contact Type]) In (1,3)) 
  AND  ((tblLookup_Reservation_Status.ReservationStatusID) In (2,3)) 
  AND  ((tblReservation_details.DateIn) Between [Forms]![frmMetricsMenu]![txtDateFrom] 
                                            And [Forms]![frmMetricsMenu]![txtDateTo]));

Ultimately using form references like this in queries will make things very complex, plus I hope you are aware of the US Date format requirement of dates in queries.

Also you will need to repeat your "AND" requirements in the OR line as well... In SQL:

WHERE (((tblLookup_Contact_types.[Contact Type]) In (1,3)) 
  AND  ((tblLookup_Reservation_Status.ReservationStatusID) In (2,3)) 
  AND  (   ((tblReservation_details.DateIn ) Between [Forms]![frmMetricsMenu]![txtDateFrom] 
                                                  And [Forms]![frmMetricsMenu]![txtDateTo])        
        OR ((tblReservation_details.DateOut) Between [Forms]![frmMetricsMenu]![txtDateFrom] 
                                                 And [Forms]![frmMetricsMenu]![txtDateTo])
       )
     );

 

[Sketchin]

No Worries, I haven't posted much SQL on the forum so I wasn't sure how people like to see it.

I am using US dates. This SQL was generated using design view in my query, as I am not well versed in writing SQL statements.

Thanks for the feedback.

 

[namliam]

The SQL "posting" isnt about a posting "people like it" it is a normal formatting to make queries readable thus understandable and maintanable.