Month

[torg]

I've 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 don't 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 you're 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 don't 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#

 

You'll have to use the Calendar object in .NET to get the last day of the month to get the #1/31/2003" since you can't hard-code "31" days. If your Date/Time column contains TIME information the above won't 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 shouldn't use - because you want to ignore the year and go off of JUST month. If that's 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 you'll be querying really.

 

One last note: Don't wrap your columns with parens. I'm not sure what you're trying to do but it looks a bit odd :) For the column named Date, you'll have to use square brackets since Date is a reserved word. You'll 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 you'll be taking a hit on performance. It won't 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)