I've been working on this project that works with a MS Access database. What I need to do with this and what I am having trouble with is I need to select a team name from a listbox and then calculate their total points.

I'm struggling to figure out how I can calculate this.
To help give you an idea of what I'm talking about the list is something like this:

control............... team
10.......................bilby
10...................... gumbles
20...................... bilby
20.......................gumbles
30.......................gumbles
(... = spaces)

So if i were to choose gumbles how do i just calculate all of the control points from the database giving me the result (from the example) 60.

Like Ramy said, use SQL functions to sum the points.

Here's a one sample code

Public Function TeamTally(ByVal TeamName As String) As Integer
  ' Return total points for TeamName
  Dim oConn As OdbcConnection
  Dim oCmd As OdbcCommand
  Dim strSQL As String
  Dim QueryResult As Object

  ' Create and open an Odbc connection
  oConn = New OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};Dbq=D:\test.mdb;Uid=;Pwd=;")
  oConn.Open()

  ' Tally points
  strSQL = "SELECT SUM(control) AS TeamTally FROM TeamPoints WHERE Team='" & TeamName & "'"

  ' Create an Odbc command
  oCmd = New OdbcCommand(strSQL, oConn)
  ' Execute command. We can use ExecuteScalar since only one value is returned
  QueryResult = oCmd.ExecuteScalar
  ' Close db connection
  oConn.Close()

  ' Test null value
  If IsDBNull(QueryResult) Then
    Return 0 ' Non-existing team, return nil
  Else
    Return CInt(QueryResult)
  End If

End Function

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
  ' Debug
  Dim a As Integer
  Dim b As Integer
  Dim c As Integer

  a = TeamTally("Bilby")
  b = TeamTally("Gumbles")
  c = TeamTally("NotFound")

  MessageBox.Show("Bilby: " & a & ", Gumbles: " & b & ", NotFound: " & c, "Points", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

I made a separate function, which returns the total points for a given team. The second sub is just for testing.

I assumed ODBC connection. You will also have to check and change connection string, table name, field names, import correct namespace and add proper error handling.

Thanks that helped me understand everything a little better but I'm still having some issues as I'm new and learning everything atm.

This is what I've been using to open up the database

connector = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = "
connector = connector & "rogaine.mdb"

I've been able to print out reports easy enough into a textfile but i couldn't get TeamTally("teamName") to print out the result to a label.

(teamName = lstTeams.SelectedItem)

Any furthur help would be much appericated and I'll continue trying to figure it all out

My sample code used Odbc but that's irrelevant.

So, you don't get a team name from a string "10...................... gumbles" which is a line from a list box?

Here's my test sub again

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
  ' Debug
  Dim a As Integer
  Dim b As Integer
  Dim c As Integer

  Dim TempStr As String
  Dim TeamName As String

  ListBox1.Items.Add("10.......................Bilby") ' Debug
  ' This is my debug line. You would use ListBox1.SelectedIndexChanged event and check if an item is selected
  ListBox1.SelectedIndex = 0 ' Debug

  ' Listbox stores Objects, convert to string
  TempStr = ListBox1.SelectedItem.ToString

  ' Do we have a fixed width columns? (First col 25 chars?)
  TeamName = TempStr.Substring(25, TempStr.Length - 25)

  ' Or do we have a variable width first column?
  Dim SepChar As String ' This is a separator character between points and names. I used dot, but you can change this to space etc.
  SepChar = "."
  Dim LastPos As Integer ' Last occurrence (position) of SepChar assuming that team name DOES NOT contain SepChar
  LastPos = TempStr.LastIndexOf(SepChar)
  If LastPos >= 0 Then
    TeamName = TempStr.Substring(LastPos + 1, TempStr.Length - LastPos - 1)
  Else
    ' SepChar not found
    TeamName = "NotFound"
  End If

  a = TeamTally(TeamName)
  b = TeamTally("Gumbles")
  c = TeamTally("NotFound")

  MessageBox.Show(TeamName & ": " & a & ", Gumbles: " & b & ", NotFound: " & c, "Points", MessageBoxButtons.OK, MessageBoxIcon.Information)

End Sub

It shows two methods to get the team name. If you use the latter, just comment out the line TeamName = TempStr.Substring(25, TempStr.Length - 25) . You could actually comment it out anyway, the code would still work.

This article has been dead for over six months. Start a new discussion instead.