search between 2 date pickers-vb 1

[aos]

Hi! Just wonder how i would carry out a search for availability between two dates. I can reserve a room for several dates but i want to be able to search for availability between two dates. At the moment i can only search one date at a time....any ideas


Private Sub DTPicker6_Change()
Dim sSQL As String
Let X = Format(DTPicker6.Value, "MM/dd/yyyy&quot

SearchGrid.Clear

Dim adoconnection As ADODB.Connection
Set adoconnection = New ADODB.Connection

adoconnection.Open ("Provider=Microsoft.jet.oledb.4.0;" & "Data Source = H:\IS4401Project\db.mdb&quot

sSQL = "Select * from BookedRooms where Date = #" & X & "#;"

Set rs = New ADODB.Recordset
rs.Open sSQL, adoconnection

SearchGrid.FormatString = &quot;<Room Number |< Guest &quot;

Dim introw As Integer
Dim rows As Integer
With SearchGrid
rows = 1
If rs.BOF Then
MsgBox &quot;No Bookings for the date selected&quot;, vbInformation, &quot;Information&quot;
Else
rs.MoveFirst

While Not rs.EOF
rows = rows + 1
rs.MoveNext
Wend
.rows = rows
.Cols = 2

introw = 1
If rs.BOF Then
MsgBox &quot;The recordset is empty. No records exist&quot;
Else
SearchGrid.Visible = True
rs.MoveFirst
While Not rs.EOF

.TextMatrix(introw, 0) = rs!RoomNo
.TextMatrix(introw, 1) = rs!Name

rs.MoveNext
introw = introw + 1
Wend
Set rs = Nothing
End If
End If
End With


End Sub

 

[olichap]

Are you by any chance reporting on an IS4 program? If so, the Fox version or Visual Basic?

 

[olichap]

I guess I could have actually tried to help answer you question before posing my own.

Sounds like you need two date fields. Then your select statement would look something like this:

&quot;Select * from BookedRooms where Date between #&quot; & X & &quot;#
and #&quot; & y & &quot;#;&quot;

 

[aos]

I'm using VB 6.0. Basically the search i'm tryin to do is when i select 2 dates on 2 date pickers, i want to check to see if rooms are available between those two dates.
Thanks!

 

[olichap]

Ok, so you have to dtPickers. Instead of putting your recordset code in the change event of each drop a command button on your form. After the user sets the date he wants, the button is clicked. In its click event you can either pass both date values directly from the pickers or set two other date variables to = their values.

Then try to populate your recordset with a BETWEEN in your sql string.

ex/&quot;Select * from BookedRooms where Date BETWEEN #&quot; & dtFrom.value & &quot;# and #&quot; & dtTo.value & &quot;#;&quot;

My own question related to which hotel PMS system you are using (when checking availability); I'm just curious and assume it is a PMS.

Oliver