VB.net, Access & date/time picker: writing sql query to find units available for reservation between 2 given dates

Posted on 2005-02-28
Medium Priority
Last Modified: 2010-08-05

As mentioned, I'm working with VB.NET Windows Form and an Access DataBase. A table in the database stores room numbers and the reservation dates for these rooms (resArrivalDate & resDepartureDate) among other things.

I have, in the Form, 2 date/time picker controlls used to pick a start and an end dates to search for availability of the rooms between the two dates selected. The results should be displayed on a datagrid controll but for some reason my query doesn't seem to work; it returns all of the rooms regardless of their availability.

Here's the code used in the search function:

        Dim vacancySearchSQL As String = "SELECT * FROM ProMARS.tblReservations WHERE resDepartureDate < " &    dtpSrchStartDate.Text & " OR resArrivalDate > " & dtpSrchEndDate.Text
        Dim vacancySearchDataAdapter As New OleDbDataAdapter(vacancySearchSQL, gblConnection)
        Dim vacancySearchDataSet As New DataSet
            vacancySearchDataAdapter.Fill(vacancySearchDataSet, "tblReservations")
        Catch ex As Exception
        End Try
            dgVacancyResults.SetDataBinding(vacancySearchDataSet, "tblReservations")
        Catch ex As Exception
        End Try

Sorry if the question seems silly, but I haven't had much experience dealing with databases and time. Your help or pointing me to a source with good information on this would be greatly appreciated. Thank you.
Question by:isrxl
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 58

Accepted Solution

amit_g earned 2000 total points
ID: 13423212
Try this ...

        Dim vacancySearchSQL As String = "SELECT * FROM ProMARS.tblReservations WHERE (resArrivalDate > " & "#" & dtpSrchStartDate.Text & "#" & " and resArrivalDate > " & "#" & dtpSrchEndDate.Text & "#" & ")" & " or " & "(resDepartureDate < " & "#" & dtpSrchStartDate.Text & "#" & " and resDepartureDate < " & "#" & dtpSrchEndDate.Text & "#" & ")"

Author Comment

ID: 13423302
Dear amit g, thank you so much, you saved the day for me.
LVL 58

Expert Comment

ID: 13423304
Glad to be of help :)

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question