Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

sum between date

Posted on 2006-05-12
16
Medium Priority
?
240 Views
Last Modified: 2010-05-01
Hi Experts,

I have a form that has a "weekofbeginning" datepicker, a "weekofending" datepicker and a "UnitID" textbox.  On "UnitId Leave", I want to execute "cmd" but at run time I get an error with my between statement.  Any help?

    Private Sub UnitID_Leave(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UnitID.Leave

        Dim conn As New System.Data.SqlClient.SqlConnection("Persist Security Info=True;Server=xxx.xxx.xxx.xxx;Database=Reporting;uid=uid;network=dbmssocn;password=password")
        Dim da As New System.Data.SqlClient.SqlDataAdapter("Select * from tblOfficerCaseLoadLog", conn)
        Dim ds As New System.Data.DataSet
        Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog WHERE unitid='" & UnitID.Text & "' & weekofbeginning='" & WeekOfBeginning.Text & "' & weekofending='" & WeekOfEnding.Text & "'", conn)

        Try
            conn.Open()
            TextBox1.Text = CType(cmd.ExecuteScalar(), String)

        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            conn.Close()
        End Try
    End Sub
0
Comment
Question by:NCSO
  • 9
  • 7
16 Comments
 

Expert Comment

by:thooi81
ID: 16670040
What was the error message prompted to you?
0
 

Author Comment

by:NCSO
ID: 16670077
Incorrect syntax near keyword 'BETWEEN'
0
 

Author Comment

by:NCSO
ID: 16670093
Sorry, here is the CMD string

        Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog WHERE unitid='" & UnitID.Text & "' & BETWEEN weekofbeginning='" & WeekOfBeginning.Text & "' AND weekofending='" & WeekOfEnding.Text & "'", conn)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Expert Comment

by:thooi81
ID: 16670222
Your SQL statement is missing some important keywords. The following is the modified SQL statement.

Dim cmd As New System.Data.SqlClient.SqlCommand("SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog WHERE unitid='" & UnitID.Text & "' AND weekofbeginning='" & WeekOfBeginning.Text & "' AND weekofending='" & WeekOfEnding.Text & "'", conn)

I do not know why you use the BETWEEN keyword. Which column are you trying to filter with the BETWEEN keyword?
0
 

Author Comment

by:NCSO
ID: 16670288
WeekOfBeginning and WeekOfEnding
0
 

Expert Comment

by:thooi81
ID: 16670402
Ok, you wan to filter the WeekOfBeginning and WeekOfEnding. But I do not know what are the values you want to filter  the WeekOfBeginning and WeekOfEnding.

I assume WeekOfBeginning field (database) will use the values from the WeekOfBeginning.Text and WeekOfEnding.Text.

And I also assume WeekOfEnding field (database) will use the values from the WeekOfBeginning.Text and WeekOfEnding.Text.

The new query will be as follow:

Dim cmd As New System.Data.SqlClient.SqlCommand( _
"SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog " & _
"WHERE unitid='" & UnitID.Text & "' " & _
"AND WeekOfBeginning BETWEEN '" & WeekOfBeginning.Text & "' AND '" & WeekOfEnding.Text & "'   " & _
"AND WeekOfEnding BETWEEN '" & WeekOfBeginning.Text & "' AND '" & WeekOfEnding.Text & "'       ", conn)

Reference notes:
SQL: BETWEEN: http://www.1keydata.com/sql/sqlbetween.html
0
 

Author Comment

by:NCSO
ID: 16670464
What I want to do is have the user put in the "WeekofBeginning" and WeekOfEnding" and then their UnitID, once they leave the UnitID textbox, run the scalar query to populate a textbox with running totals for the time period specified in weekofbeginning and weekofending.
0
 

Expert Comment

by:thooi81
ID: 16670602
If I am wrong, please do correct me. Actually, you are trying to use the values from the vb control boxes (WeekOfBeginning.Text and WeekOfEnding.Text) to filter out the correct period during the execution of SQL statement. So that the Cmd can return the valid total amount.

I do not know 1 date or 2 date field(s) is/are in the physical table.

I assume that the physical table consists of WeekOfBeginning and WeekOfEnding fields.
My questions are:
1. if the date range from one of the records in the physical table is BEFORE the date range from vb controls, then should the record be considered?

2. if the date range from one of the records in the physical table is PART BEFORE and PART WITHIN the date range from vb controls, then should the record be considered?

3. if the date range from one of the records in the physical table is PART AFTER  and PART WITHIN the date range from vb controls, then should the record be considered?

4. if the date range from one of the records in the physical table is AFTER the date range from vb controls, then should the record be considered?

0
 

Author Comment

by:NCSO
ID: 16670727
I see where I have messed this up, I do have a WeekOfBeginning and a WeekOfEnding field in the table, but I see why the between statement won't work between (2) seperate fields in the table.  I have made the following chages in the database.  I now have a "DateAdded" field and I have removed the WeekOfBeginning and the WeekOfEnding fields.  Would I change the scalar query to the following to get it to work?

        'Dim cmd As New System.Data.SqlClient.SqlCommand( _
        '"SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog " & _
        '"WHERE unitid='" & UnitID.Text & "' " & _
        '"AND DateAdded BETWEEN '" & WeekOfBeginning.Text & "' AND '" & WeekOfEnding.Text & "'", conn)

Does this make more sense and am I on the right track now.  I apologize for my ignorant mistake.
0
 

Expert Comment

by:thooi81
ID: 16670880
Yes, you are almost at the finish line.

Just to be sure your SQL statment works more accurately, I suggest that the date need to be literal which means that convert the date to a standard format. Different database server may have different date format. Please be aware of this. The example below is valid for Microsoft SQL server.

Dim DateFrom As String
Dim DateTo As String

DateFrom = "{d '" & _
                  Year(CDate(WeekOfBeginning.Text)) & "-" & _
                  Right$("0" & Month(CDate(WeekOfBeginning.Text)), 2) & "-" & _
                  Right$("0" & Day(CDate(WeekOfBeginning.Text)), 2) & _
                  "'}"
DateTo = "{d '" & _
                  Year(CDate(WeekOfEnding.Text)) & "-" & _
                  Right$("0" & Month(CDate(WeekOfEnding.Text)), 2) & "-" & _
                  Right$("0" & Day(CDate(WeekOfEnding.Text)), 2) & _
                  "'}"

        Dim cmd As New System.Data.SqlClient.SqlCommand( _
        "SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog " & _
        "WHERE unitid='" & UnitID.Text & "' " & _
        "AND DateAdded BETWEEN " & DateFrom & " AND " & DateTo, conn)

Reference notes:
http://www.bigbold.com/snippets/posts/show/756
0
 

Author Comment

by:NCSO
ID: 16672230
The Right$ is throwing an error at debug.
0
 

Accepted Solution

by:
thooi81 earned 1200 total points
ID: 16672715
Ok, I think you are using the VB .NET instead of VB 6.0.
You must use Microsoft.VisualBasic.Right function instead of Right$ function

DateFrom = "{d '" & _
                  Year(CDate(WeekOfBeginning.Text)) & "-" & _
                  Microsoft.VisualBasic.Right("0" & Month(CDate(WeekOfBeginning.Text)), 2) & "-" & _
                  Microsoft.VisualBasic.Right("0" &    Day(CDate(WeekOfBeginning.Text)), 2) & _
                  "'}"
DateTo = "{d '" & _
                  Year(CDate(WeekOfEnding.Text)) & "-" & _
                  Microsoft.VisualBasic.Right("0" & Month(CDate(WeekOfEnding.Text)), 2) & "-" & _
                  Microsoft.VisualBasic.Right("0" &    Day(CDate(WeekOfEnding.Text)), 2) & _
                  "'}"

        Dim cmd As New System.Data.SqlClient.SqlCommand( _
        "SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog " & _
        "WHERE unitid='" & UnitID.Text & "' " & _
        "AND DateAdded BETWEEN " & DateFrom & " AND " & DateTo, conn)
0
 

Author Comment

by:NCSO
ID: 16681541
I now get the following:

'Day' is a type and can not be used as an expression
0
 

Author Comment

by:NCSO
ID: 16684749
I am almost there, however I get the 'day' is a type and can not be used as an expression
0
 

Author Comment

by:NCSO
ID: 16685194
Thanks for all you help!
0
 

Expert Comment

by:thooi81
ID: 16719784
I forgot again that you are using the VB.NET. You can easily format the date value from vb .net control

        DateFrom = " '" & WeekOfBeginning.ToString("yyyy-MM-dd") & "' " 'Return a formatted date string
        DateTo =  " '" & WeekOfEnding.ToString("yyyy-MM-dd") & "' "

        Dim cmd As New System.Data.SqlClient.SqlCommand( _
        "SELECT sum (WrittenCode1) AS totalcode1 FROM tblofficerCaseLoadLog " & _
        "WHERE unitid='" & UnitID.Text & "' " & _
        "AND DateAdded BETWEEN " & DateFrom & " AND " & DateTo, conn)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question