DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Help querying dates

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

    Help querying dates

    [Originally posted by Jimmy]

    I have records in my database with date (short date, ex:10/25/01) field in it. I want to run a query that would display for example, all records within Feb. I added a combo box and added in the list all the months from Jan - Dec. How would I query so that when I select a month in the combo box, all records for that month shows. I want to keep the date field property to short date. The only query I know for dates is:
    SELECT * FROM MyTable WHERE MyDate BETWEEN #10/01/01# AND #10/30/30# ORDER BY MyDate etc....

    I don't know how to implement this into my combo box. Is there a better way?


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

    Re:Help querying dates

    [Originally posted by Santiago Linero]

    I don´t think you can implement it that way, you will need to translate the chosen month from the Combo into another variable (ie. :
    Select Case Combo1.Text
      case "JAN"
          vMonth = 1
      case "FEB"
          vMonth = 2
      ...
    end select
    )
    This way, you can select

    SELECT * FROM MyTable WHERE Month(MyDate)= vMonth etc....

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

    Re:Re:Help querying dates

    [Originally posted by Robert]

    maybe i can help

    try splitting your date in three different fields within your database

    ie
    MONTH  DAY    YEAR
    And Also Within Your Combo Box Add The same
    Month
    Year
    Day
    and call your combo box  cbo1
    next call your List Box  cbo2

    And Add The Following To Your Search Button
    cbo1val = cbo1
    Select Case cbo1val
        Case "Month"
        what = "Month"
        Case = "DAY"
        what = "Day"
        Case = "Year"
        what = "Year"
        Case Else
        what = "ERROR"
        END SELECT
        If Not what = "ERROR" Then
       
        Dim conn As ADODB.Connection
        Dim rs As ADODB.Recordset
       
        Set conn = New ADODB.Connection
        Set rs = New ADODB.Recordset
        Provider=Microsoft.Jet.OLEDB.4.0;DataSource= path 
    conn.Open connstr
        rs.Open "SELECT * FROM Agent WHERE [" & what & "] LIKE '" & Text1.Text & "' ORDER BY [" & what & "] ASC", conn
        If Not rs.EOF Then
            cbo2.Clear
            i = 0
            Do While Not rs.EOF
                txt2 = txt2 & rs("Field1") & ";"
                txt3 = txt3 & rs("Field2") & ";"
                txt4 = txt4 & rs("Field3") & ";"
                txt5 = txt5 & rs("Field4") & ";"
                txt6 = txt6 & rs("Field5") & ";"
                txt7 = txt7 & rs("Field6") & ";"
               
    newval = CStr(i) & "). " & rs("Field1") & " " & rs("Field2")
                cbo2.AddItem newval
                i = i + 1
                rs.MoveNext
            Loop
            w1 = Split(txt2, ";")
            w2 = Split(txt3, ";")
            w3 = Split(txt4, ";")
            w4 = Split(txt5, ";")
            w5 = Split(txt6, ";")
            w6 = Split(txt7, ";")
           
        Else
            MsgBox "No Records Found!"
        End If
        Set rs = Nothing
        conn.Close
    Else
    MsgBox "Select a Search!"
    End If
    End If

    I Think I Can Make It Look Better And Easier If I Email It To You

    If You Want Me Too
    Just Send Me An email And I Will Reply With All The Things You Need Help With And That I Know


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