Results 1 to 7 of 7

Thread: How to Filter A database monthly?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    104

    Unhappy How to Filter A database monthly?

    Hi everyone

    {VB 6.0}
    I want to filter my database monthly. I want to see my data of the month which I want to see. I haven't any code for it
    please help me to search or filter my data of one month {Or } How to see data from a date to another date?

    Sincerely
    #MKhP7

  2. #2
    PowerPoster
    Join Date
    Feb 2012
    Location
    West Virginia
    Posts
    14,205

    Re: How to Filter A database monthly?

    You would want the SQL Between operator

    http://www.w3schools.com/sql/sql_between.asp

    Scroll down close to the bottom for example of how to use with dates.

  3. #3
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to Filter A database monthly?

    An alternative to the Between-Operator is available too, with the DatePart-function
    (or the derived functions Year(), Month() which are using DatePart under the covers).

    e.g. an SQL-Select like:

    Select Count(ClickDate), DatePart('yyyy',ClickDate), DatePart('m',ClickDate)
    From ClicksTable
    Where Page='www.somedomain.com/SomePage.html'
    Group By DatePart('yyyy',ClickDate), DatePart('m',ClickDate)

    ... will list the sumed-up Counts of Page-Clicks - aggregated and grouped for each Year and Month.

    Olaf

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    104

    Re: How to Filter A database monthly?

    Can We do the same in MS Access data table?

  5. #5
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

    Re: How to Filter A database monthly?

    Quote Originally Posted by Mahmood Khaleel Pira View Post
    Can We do the same in MS Access data table?
    Yes of course, here's an example that shows that it works:

    Into a Form (no References needed, since ADO is used latebound)
    Code:
    Option Explicit
     
    Private Cnn As Object, DBName, i
    
    Private Sub Form_Load()
      Caption = "Click Me, to perform the Select"
      DBName = Environ("temp") & "\test1.mdb"
      If CreateObject("Scripting.FileSystemObject").FileExists(DBName) Then Kill DBName
     
      Set Cnn = CreateObject("ADOX.Catalog").Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName)
          Cnn.CursorLocation = 3 'adUseClient
          Cnn.Execute "Create Table Clicks(URL Text, ClickDate DateTime)"
      
      Dim Rs As Object
      Set Rs = CreateObject("ADODB.Recordset")
          Rs.Open "Select * From Clicks Where 1=0", Cnn, 3, 3
      For i = 1 To 90 'instert data for 90 days (backwards from today)
        Rs.AddNew
          Rs!URL = "SomeDomain.com/Page" & i & ".html"
          Rs!ClickDate = Now - i
        Rs.Update
      Next
    End Sub
    
    Private Sub Form_Click()
      AutoRedraw = True: Cls
      Dim SQL(0 To 20) As String
          SQL(0) = "Select Count(ClickDate) As C, DatePart('yyyy',ClickDate) As Y, DatePart('m',ClickDate) As M"
          SQL(1) = "From Clicks Group By DatePart('yyyy',ClickDate),   DatePart('m',ClickDate)"
      
      With Cnn.Execute(Join(SQL, vbCrLf)) 'this retrieves a read-only-Rs and print it to the Form
        For i = 0 To .Fields.Count - 1: Print .Fields(i).Name,: Next: Print
        Do Until .EOF
          For i = 0 To .Fields.Count - 1: Print .Fields(i).Value,: Next: Print
          .MoveNext
        Loop
      End With
    End Sub
    HTH

    Olaf

  6. #6

    Thread Starter
    Lively Member
    Join Date
    Aug 2016
    Posts
    104

    Re: How to Filter A database monthly?

    Quote Originally Posted by Schmidt View Post
    Yes of course, here's an example that shows that it works:

    Into a Form (no References needed, since ADO is used latebound)
    Code:
    Option Explicit
     
    Private Cnn As Object, DBName, i
    
    Private Sub Form_Load()
      Caption = "Click Me, to perform the Select"
      DBName = Environ("temp") & "\test1.mdb"
      If CreateObject("Scripting.FileSystemObject").FileExists(DBName) Then Kill DBName
     
      Set Cnn = CreateObject("ADOX.Catalog").Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DBName)
          Cnn.CursorLocation = 3 'adUseClient
          Cnn.Execute "Create Table Clicks(URL Text, ClickDate DateTime)"
      
      Dim Rs As Object
      Set Rs = CreateObject("ADODB.Recordset")
          Rs.Open "Select * From Clicks Where 1=0", Cnn, 3, 3
      For i = 1 To 90 'instert data for 90 days (backwards from today)
        Rs.AddNew
          Rs!URL = "SomeDomain.com/Page" & i & ".html"
          Rs!ClickDate = Now - i
        Rs.Update
      Next
    End Sub
    
    Private Sub Form_Click()
      AutoRedraw = True: Cls
      Dim SQL(0 To 20) As String
          SQL(0) = "Select Count(ClickDate) As C, DatePart('yyyy',ClickDate) As Y, DatePart('m',ClickDate) As M"
          SQL(1) = "From Clicks Group By DatePart('yyyy',ClickDate),   DatePart('m',ClickDate)"
      
      With Cnn.Execute(Join(SQL, vbCrLf)) 'this retrieves a read-only-Rs and print it to the Form
        For i = 0 To .Fields.Count - 1: Print .Fields(i).Name,: Next: Print
        Do Until .EOF
          For i = 0 To .Fields.Count - 1: Print .Fields(i).Value,: Next: Print
          .MoveNext
        Loop
      End With
    End Sub
    HTH

    Olaf
    I meant making a vb6 database, in search window how to see our data from one month. I didn't mean online or web database.
    i have a vb6 database project and I want to add a form for searching data, And I want see my data in a month. Or seeing data from "First Selected Date" till "Second Selected Date"

  7. #7
    PowerPoster Arnoutdv's Avatar
    Join Date
    Oct 2013
    Posts
    5,872

    Re: How to Filter A database monthly?

    It's just a sample which creates a temporary database, there is no reference to an online/web database.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width