-
Feb 4th, 2017, 02:31 PM
#1
Thread Starter
Lively Member
-
Feb 4th, 2017, 04:55 PM
#2
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.
-
Feb 4th, 2017, 07:09 PM
#3
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
-
Feb 5th, 2017, 04:06 AM
#4
Thread Starter
Lively Member
Re: How to Filter A database monthly?
Can We do the same in MS Access data table?
-
Feb 5th, 2017, 08:22 PM
#5
Re: How to Filter A database monthly?
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
-
Feb 6th, 2017, 10:35 AM
#6
Thread Starter
Lively Member
Re: How to Filter A database monthly?
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"
-
Feb 6th, 2017, 11:15 AM
#7
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|