Counting records

[torg]

I wish to find all records between two dates. There can be more than one record per day. Some days i have`nt registerd any records.
Today (. I`m assuming the system date is 2nd of may)(2nd of May, if I register a record, This entry will not show, but records from last mont will show. If I had registered also at record 1st of may yesterday`s records would show, but not the reciord for today(2nd may).Records from last mont would also show. If I had regisered records for 1st of may and 8 of may(. I`m assuming the system date is 8th of may), only these two would show(This is not records for 10 days, only 8 .I`m assuming the system date is 8th of maydays). After the 11th there are no problem(assuming today`s system date is 11th)
I`m suspecting my query is wrong to accomplis what i want.
My problem description may be hopelessly idiotic , but hope someone understand my problem an is able to help me.
If You have other Ideas to accomplis my goal to be able to show records from the 10 last days, not the 10 last records, I`d appreciate all help I can get.

 Public Sub Fill_dataset()

        If dgView.Height > 100 Then
            <<clear datagrid
            OleDbConnection.Open()
            dsInCome.Clear()
            dsIncome.Reset()
            OleDbConnection.Close()
        Else
        End If

        
        Dim myDateAs String
        myDate= Now.ToShortDateString
        myDate= myDate.Replace(".", "/")

        Dim d As String <<day
        Dim M As String = CStr(Now.Month) <<Month
        Dim y As String = CStr(Now.Year) <<Year
               If CDbl(Now.Day) <= 10 Then
            Dim days As Double
              days = CDbl(Now.Day - 1)
            d = CStr(Now.AddDays(-days).Day)
        Else
            d = CStr(Now.AddDays(-10).Day)
        End If

        Dim FirstDayOfPeriod As String
        FirstDayOfPeriod = M + "/" + d + "/" + y

        Me.OleDbConnection.Open()
        adIncome = New OleDb.OleDbDataAdapter("SELECT TypeOfIncome, From, Dato, Comment, Amount from tblIncome
WHERE tblIncome.Dato BETWEEN #" & MyDate & "# AND #" & 

FirstDayOfPeriod & "# order by Dato DESC;", Me.OleDbConnection)
        Dim dtIncome As DataTable
        Try
            adIncome.Fill(dsIncome, "dtIncome")
        Catch ex As OleDb.OleDbException
            MessageBox.Show(ex.Message)
        End Try

         dgView.Height = dsIncome.Tables("dtIncome").Rows.Count * 50 
        frmUtgift.ActiveForm.Height = 568 220 + dgView.Height + 80
        dgView.DataSource = dsIncome.Tables("dtIncome").DefaultView << gets a customized view of table

        Me.OleDbConnection.Close()
end sub

 

[APaule]

As far as I can see, you just subtracted from the date but return just the day to your variable d.
When you now assemble your date you still have the old month and year (from the actual date).
So it is obvious that this just works correctly when todays day-number is bigger than the number you subtract from it.

 

[torg]

I hear what you say, but can`t se the solution. So if anybody could give me a code examle, it would be appreciated

 

[torg]

I solved my problem

Public Sub Fyll_datasett()

         Me.Height = 552

        Dim MyDate As String
        MyDate= Now.ToShortDateString
        MyDate= MyDate.Replace(".", "/")

        Dim moonth As String = CStr(Now.Month)
        Dim daay As String = CStr(Now.Day)
        Dim yeer As String = CStr(Now.Year)

        MyDate = CStr(moonth) + "/" + CStr(daay) + "/" + CStr(yeer)
        Dim d As Double
        Dim M As Double
        Dim y As Double

        Dim daysInMonth(11) As Double
        daysInMonth(0) = 31
        daysInMonth(1) = 28 
        daysInMonth(2) = 31
        daysInMonth(3) = 30
        daysInMonth(4) = 31
        daysInMonth(5) = 30
        daysInMonth(6) = 31
        daysInMonth(7) = 30
        daysInMonth(8) = 31
        daysInMonth(9) = 30
        daysInMonth(10) = 31
        daysInMonth(11) = 30

        Dim FirstDayOfPeriod As String
        d = Now.Day - 10
        M = Now.Month
        y = Now.Year

        If d <= 0 Then <<if today`s day - 10 days is less or equal to 0 then perform. No date is less or equal to 0
            M = M - 1
            If daysInMonth(Now.Month) Mod 2 = 0 Then
                If month has 28 or 30 days,then execute
                Dim s As Double

                <<If today`s day is the 2nd in month , then we will
                 find out
                how many days left which we`ll have to subtract from   
               previous month then
                 This gives 8 days left from last month  + 2 = 10 days withdrawn from today`s date

                s = 10 - Now.Day
                FirstDayOfPeriod = CStr(M) + "/" + CStr(daysInMonth(Now.Month) - s) + "/" + CStr(y)
                Me.OleDbConnection.Open()

              FirstDayOfPeriod)
                adInntekt = New OleDb.OleDbDataAdapter("SELECT TypeofIncome, From, Dato, comment, amountfrom tblIncome WHERE tblIcome.Dato BETWEEN #" & MyDate & "# AND #" & FirstDayOfPeriod & "# order by Dato DESC;", Me.OleDbConnection)
                Try
                    adInntekt.Fill(dsIncome, "dtIncome")
                Catch ex As OleDb.OleDbException
                    MessageBox.Show(ex.Message)
                End Try
                frmIncome.ActiveForm.Height = 568
                dgView.DataSource = dsIncome.Tables("dtIncome").DefaultView << gets a customized view of table
                Me.OleDbConnection.Close()
                Exit Sub
            ElseIf daysInMonth(Now.Month) Mod 2 <> 0 Then
                If month has 29 or 31 days,then execute
                Dim s As Double

.
.
.
.
.

 

[APaule]

Maybe I misunderstood you, but I thought you just wanted the records from the 10 last days.
So why dont you just write now.adddays(-10), and then put it into the correct format for your query?

By the way; with your code you will get problems in january!

 

[torg]

Good question, why don`t I use now.addays(-10)...
If I just had remembered I know I had a reason. But which reason? .. . MyDate is of the format MM/DD/YYYY. If I use Now.addDays(-10) for the variable FirstDayOfPeriod the format would be: MM/DD/YYYY HH:MM:SS. Therefore the MyDate - FirstDayOfPeriod sends an errormessage. There are probably an easy way of solving this, I live in Norway. The correct dateformat here is: DD/MM/YYYY, I therefore have to convert to MM/DD/YYYY. Maybe it`s all baloney. Any comment are appreciated...
The january problem were fixed

 

[APaule]

myDate = Now.Month.ToString & "/" & Now.Day.ToString & "/" & Now.Year.ToString
Dim FD as Date = Now.AddDays(-10)
FirstDayOfPeriod = FD.Month.ToString & "/" & FD.Day.ToString & "/" & FD.Year.ToString

Three lines of code, accomplishing the whole task. So, where is the real problem?

In case you dont need the time you could use Today instead of Now.

 

[torg]

Is it possible to get more embarrassed ?(rhetorical quesiton)
Thank you. That worked fine....

 

[APaule]

Youre welcome... and never mind.
You know the old saying: You cant see the forest for the trees
Believe me. I know that very well. Sometimes this happens to me as well.