An easy question for All VB Masters

[RohanWest]

Hi there, i need some help with this problem....


SELECT * FROM [Date] WHERE (date.date = #datevar#) AND (Date.RoomId = Roomvar)

datevar = date()
do...
make date
create SQL
if record count <> 3 then
add date to list
end if
loop until (365 dates checked)

I am trying to a rooms availablity for a date, if the room is free then add it to a list box.

The method listed above is really slow because you have to keep generating a record source for each date.

I you have any ideas then let me know...

Rohan

[HeSaidJoe]

not sure I understand what you are trying to do.
if in your database you have roomnumber and datebooked, and you default your datebooked to 0000/00/00 if not booked couldn't you just sql for all dates = 0000/00/00 and then just list those rooms as they would be free.

[RohanWest]

We need to show a list of dates, we just need to speed the process up.

[Iain17]

Not an expert at SQL so i can't write the statment for you but here is a thought.

Get all of the dates for the room in one go.

SELECT * FROM [date] WHERE Date.Roomid = RoomVar.

Now loop through the all of the dates the room could be booked for, and check the recordset to see if the room is booked.

e.g

'loop for a year
do
  if checkAvailabilyt(date) = true then
    list1.add (date)
   end if
until year done

private function checkAvailabilty (date) as boolean
  rs.movefirst
  
  checkavailabilty = true

  do while not rs.eof
    if rs!Date = date then
      checkavailabilty = false
      exit function
    end if
    rs.movenext
  loop
end sub

[Iain17]

Or an even quicker way to fill the list box

This sub won't work as it stands, you will probably have to make some alterations to make it work exactly right, but it should give you a general idea.

Private Sub fillListBoxWithDates()
    Dim i As Integer, iLoopLen As Integer
    Dim dtStart As Date, dtEnd As Date
    
    rs.movefirst
    
    'firstdate is the date that would go in the
    'list box if it was available
    dtStart = firstdate 
    Do Until rs.EOF
      'get the date of this record
      dtEnd = rs!Date
      
      'now you want to loop for the number of dates
      'between the first date and the date in this record
      'and fill them into the list box
      
      iLoopLen = DateDiff("d", dtStart, dtEnd)
      For i = 1 To iLoopLen 'may need -1
        List1.AddItem DateAdd("d", i, dtStart) 'add dates
      Next i
      
      'set the loop start to the date after the
      'date in the record
      dtStart = DateAdd("d", 1, dtEnd)
      rs.movenext
    Loop
    
    'if the loop start date is less than the lastdate
    'then you want to fill the list box with the rest
    'of the dates.

    'lastdate is the last available date for the room
    'to be booked for, say a year ahead of the firstdate
    iLoopLen = DateDiff("d", dtStart, lastdate)
    
    For i = 1 To iLoopLen
      List1.AddItem DateAdd("d", i, dtStart)
    Next i
    
End Sub

n.b Make sure the record set is ordered by the date.