DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: How to select a date range using VB sql

  1. #1
    Join Date
    Aug 2004
    Posts
    43,023

    How to select a date range using VB sql

    [Originally posted by lime]

    hey, everyone

    I tried to write a sql statement to select a date range that is from a date to another date. Eg: 01/03/2003 to 31/03/2003

    This is what i had wrote:-

    Dim frdate As Date
    Dim todate As Date
    frdate = Format$(txtFrom, "dd/mm/yyyy")
    todate = Format$(txtTo, "dd/mm/yyyy")

    SQL = &quot;select * from qrtgst where bdate >= #&quot; & frdate & &quot;# and bdate <= #&quot; & todate & &quot;#&quot;

    It is giving the date of March and also 03/01/2003 and also the date of January.(which should not be appearing).

    I had already check the regional setting in the control panel. I had set the date to English(US). Date format to short date format &quot;dd/mm/yyyy&quot;. I had also set bdate field in qrtqst table to short date format.
    &quot;dd/mm/yyyy&quot;


    I am using visual basic 6.0, ms access 97 and win 98

  2. #2
    Join Date
    Aug 2004
    Posts
    43,023

    Re:How to select a date range using VB sql

    [Originally posted by Larry Asher]

    First of all I would ensure that I am writting a date value. I don't think it is necassary to use the Format() function because that is only useful for displaying the date not storing. So, I would try this,

    Dim frdate As Date
    Dim todate As Date
    frdate = CDate(txtFrom)
    todate = CDate(txtTo)

    Or you might want to ensure that the value in the text box is in a proper date format using the IsDate() function, example:

    Dim frdate As Date
    Dim todate As Date

    ' Test for date
    If Not IsDate(txtFrom.Text) Then
    ' Exit Sub
    MsgBox "The From date must be a proper date format. Try MM/DD/YYYY.", vbExcalamation, "Improper Date Format"
    End If
    ' Test for date
    If Not IsDate(txtTo.Text) Then
    ' Exit Sub
    MsgBox "The From date must be a proper date format. Try MM/DD/YYYY.", vbExcalamation, "Improper Date Format"
    End If
    ' Covert Date
    frdate = CDate(txtFrom.Text)
    todate = CDate(txtTo.Text)
    ' See what the computer see's - Debug only
    MsgBox "From date = " & frdate & vbCrLf & "To date = " & todate

    Your SQL statement is probably fine but I like using the Between operator especially with dates it is inclusive of the limits. Example:

    SQL = "SELECT * FROM qrtgst WHERE bdate BETWEEN #" & frdate & "# AND #" & todate & "#"


    Just one more thing I have noticed some very odd results when working with date using the MM/DD/YYYY format and the DD/MM/YYYY format one of the formats does not produce the desired results. So, If after running the code and you do not see the correct date you entered displayed in the message box try entering the date as MM/DD/YYYY.

    Let me know how it turns out, best of luck.



  3. #3
    Join Date
    Aug 2004
    Posts
    43,023

    Re:How to select a date range using VB sql

    [Originally posted by Jim]

    If you are certain the date entered is in the dd/mm/yyyy format, you could change the query statment to use the format function using the 3 char month.

    eg
    SQL = "select * from qrtgst where bdate >= #" & format(frdate,"dd-mmm-yyyy") & "# and bdate <= #" & format(todate,"dd-mmm-yyyy") & "#"

  4. #4
    Join Date
    Aug 2004
    Posts
    43,023

    Re:How to select a date range using VB sql

    [Originally posted by mark]

    Have you tried something like this within you sql statement?

    "select * from encpro where encpro.epdate Between #" & begindate.Text & "# AND #" & enddate.Text & "#"

    Hope this helps - M.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center