Month

[torg]

Ive got the following code. I wish to be able to select income for a spesific month. as it is now, I`ll pick Income for on specific day in month, not the total for the whole month. How can I achieve this? It`s an Access database
i.e if I`ve got dtpDate.value = 22.01.2002 (month value would be 1)
and tblIncome.Date = 22/01/2002 (mont value would be 01 if I`m not wrong.)

Dim myString As String 
        Dim aString As String 
        myString = (dtpDate.Value) 
        aString = (Replace(myString, ".", "/")) 

        MessageBox.Show(aString) 
         Exit Sub 
        Try 
            Me.OleDbConnection.Open() 
            adIncome = New OleDb.OleDbDataAdapter("select (tblIncome.Amount) as Tot FROM tblIncome where (tblncome.date) = #" & aString & "#;", Me.OleDbConnection) 
            adInntekt.Fill(dsIncome, "dtIncome") 
            dgView.DataSource = dsInntekt.Tables("dtIncome") 
        Catch ex As OleDb.OleDbException 
            MessageBox.Show(ex.Message) 
        End Try 
        Me.OleDbConnection.Close()

 

[Nerseus]

Normally you would do this with two dates and BETWEEN or a combination of >= and <=. If you use a calculation based on Month, you will run into performance problems unless you store the month as a separate column.

For example:

Given your query, you could use something like the follwing - but you dont want to, trust me:

SELECT tblIncome.Amount FROM tblIncome WHERE Month(tblIncome.[Date]) = 1

You could get the "1" in .NET pretty easy, from a Date variable.

The problem with the above is that youre using the Month function on a column. This means that the database must grab every month in the table which involves some kind of conversion to figure out what the month is. An alternative would be to store the Month as a separate column, stored as a Number filed (or int in SQL Server) and query off of that instead. This is typical in a reporting database where you want performance and dont mind carrying around an extra column to support faster queries.

The more typical query would do the following:

SELECT tblIncome.Amount FROM tblIncome WHERE tblIncome.[Date] >= #1/1/2003# AND tblIncome.[Date] < #2/1/2003#

The #1/1/2003# and #2/1/2003# can be calculated easily given your month of 1. If you need sample code on how to do this in .NET, let me know.

Assuming you have no TIME portion in your Date/Time columns, you could also use BETWEEN, as in:

SELECT tblIncome.Amount FROM tblIncome WHERE tblIncome.[Date] BETWEEN #1/1/2003# AND #1/31/2003#

Youll have to use the Calendar object in .NET to get the last day of the month to get the #1/31/2003" since you cant hard-code "31" days. If your Date/Time column contains TIME information the above wont work since a date/time of "1/31/2003 1:00PM" will come AFTER "1/31/2003" which assume a time of midnight. If you need more info, let me know

As a note: If you want data for a particular month regardless of year, you will HAVE to use the first query - the one I said you shouldnt use - because you want to ignore the year and go off of JUST month. If thats the case, you may want to consider adding a Month column that gets updated every time you Insert or Update a row. It depends on the amount of data youll be querying really.

One last note: Dont wrap your columns with parens. Im not sure what youre trying to do but it looks a bit odd For the column named Date, youll have to use square brackets since Date is a reserved word. Youll want the brackets around the column name as in: tblIncome.[Date]

-Nerseus

 

[torg]

ok, thank you

 

[torg]

If anybody cares, I solved my broblem like this without concidering year.

 Dim myDate As String = dtpDato.Text
        Dim M As String = DatePart(DateInterval.Month, dtpDato.Value)
        Me.OleDbConnection.Open()
        adUtgiftspost = New OleDb.OleDbDataAdapter("SELECT sum(tblUtgift.belop) as Test from tblUtgift WHERE month(tblUtgift.Dato)= " & M & ";", Me.OleDbConnection)
        Try
            adUtgiftspost.Fill(dsUtgiftspost, "dtUtgiftspost")
        Catch ex As OleDb.OleDbException
            MessageBox.Show(ex.Message)
        End Try
        Me.OleDbConnection.Close()

 

[Nerseus]

As I said, you could do that but youll be taking a hit on performance. It wont be a big deal for small amounts of data. but for anything large this would be unacceptable because of the reasons stated above.

Good luck!
-nerseus

 

[torg]

I suppose it was something like this you suggested(?)

Dim MyDate As String = dtpDato.Value
        MyDate = Format(dtpDato.Value, "M/d/yyyy")
        MyDate = Replace(MyDate, ".", "/")
        Dim d As String = "1"
        Dim M As String = dtpDato.Value.Month
        Dim y As String = dtpDato.Value.Year
        Dim FirstDayOfMonth As String
        FirstDayOfMonth = M + "/" + d + "/" + y
.
.
.
.

adUtgiftspost = New OleDb.OleDbDataAdapter("SELECT sum
(tblUtgift.belop) as Test from tblUtgift WHERE tblUtgift.Dato BETWEEN #" & MyDate & "# AND #" & FirstDayOfMonth & "#;", Me.OleDbConnection)