How to Filter A database monthly?

Hi everyone :wave:

:check: {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

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.

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

Can We do the same in MS Access data table?

Quote:

---

Originally Posted by Mahmood Khaleel Pira

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

Quote:

---

Originally Posted by Schmidt

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"

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