S.O.S - Problems with SQL query...

[vangundy]

I have been working on this for two weeks now and I am very frustrated!!! Here is what I am trying to do:

A person wants to reserve a monitor. In my database I have Date_Out and Date_Return fields. The user selects the dates he wants to reserve the monitor (Date1 = the date he would like to take out the monitor and Date2 = the date he would be returning the monitor. If the dates (date1 and date2) he selected are within the period of Date_Out and Date_Return, then he is prompted with the msg that the dates he selected for the monitor are not available. If the monitor is available then do a INSERT query into Table1


varDate1 = #03/04/2005#
varDate2 = #03/04/2005#

sqltext = "SELECT Table1.SerialNo, Table1.Date_Out, Table1.Date_Return FROM Table1 WHERE Table1.SerialNo='" & varSerialNo & "' AND (Table1.Date_Out >= #" & varDate1 & "# And Table1.Date_Out <= #" & varDate1 & "#) OR (Table1.Date_Out >= #" & varDate2 & "# And Table1.Date_Out <= #" & varDate2 & "#)"

rs.open sqltext,conn

if not rs.eof then
Response.Write "The monitor is not available!"
else
Response.Write "The monitor is available!"
end if

 

[ByteMyzer]

The SQL line should read:

sqltext = "SELECT Table1.SerialNo, Table1.Date_Out, Table1.Date_Return " _
    & "FROM Table1 " _
    & "WHERE Table1.SerialNo='" & varSerialNo & "' " _
    & "AND (Table1.Date_Out <= #" & varDate1 & "# " _
    & "And Table1.Date_Return >= #" & varDate1 & "#) " _
    & "OR (Table1.Date_Out <= #" & varDate2 & "# " _
    & "And Table1.Date_Return >= #" & varDate2 & "#);"

The above SQL criteria assumes that you do not intend an item to be checked out on the returned date. Otherwise, use:

sqltext = "SELECT Table1.SerialNo, Table1.Date_Out, Table1.Date_Return " _
    & "FROM Table1 " _
    & "WHERE Table1.SerialNo='" & varSerialNo & "' " _
    & "AND (Table1.Date_Out <= #" & varDate1 & "# " _
    & "And Table1.Date_Return > #" & varDate1 & "#) " _
    & "OR (Table1.Date_Out <= #" & varDate2 & "# " _
    & "And Table1.Date_Return > #" & varDate2 & "#);"

See if this works for you.

 

[vangundy]

I modified my query as :

sqltext = "SELECT Table1.SerialNo, Table1.Date_Out, Table1.Date_Return FROM Table1
WHERE Table1.SerialNo='" & varSerialNo & "'
AND (#" & varDate1 & "# Between [date_out]
And [date_return])
OR (#" & varDate2 & "# Between [date_out]
And [date_return])
OR ([Date_Out] Between #" & varDate1 & "#
And #" & varDate2 & "#)
OR ([Date_Return] Between #" & varDate1 & "#
And #" & varDate2 & "#)"

If I enter
varDate1 = #01/04/2005#
varDate2= #01/04/2005#

Nothing appears which is perfect!

If I enter
varDate1 = #01/04/2005#
varDate2= #05/04/2005#
The page displays results which is perfect!

If I enter
varDate1 = #03/04/2005#
varDate2= #03/04/2005#

I should get something displaying because in the access database Table1, the Date_Out is 03/04/2005 and Date_Return is 03/04/2005... Both should result as true..

Any ideas why?

 

[cmmrfrds]

Are the dates in the format mm/dd/yyyy? If not this is usually the default format. Unless you have the windows setting set to something else. Be careful of the TIME part of the date with between. To ignore the time portion of the date format the date to short date.

AND (#" & Format(varDate1,"mm/dd/yyyy") & "# Between [date_out]
And [date_return])