missing operator in query sum expression vb net

Question

Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select sum[Shark Individual Weight] from FishCaught'", myConnection)
Dim ds As DataTable = New DataTable()
da.Fill(ds)
DataGridView1.DataSource = ds

myConnection.Close()

Hello. I am using vb.net connecting to ms access. I keep getting error 'missing operator in query expression'. I would like to know what's wrong with my sql statement.

    Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select Sum([Shark Individual Weight]) From FishCaught Where ([OperationID]) = '" & TextBoxOpID4.Text & "'", myConnection)
    Dim ds As DataTable = New DataTable()
    da.Fill(ds)
    DataGridView2.DataSource = ds

I think my SQL syntax is wrong. How should write it actually? I get this error 'Data type mismatch in criteria expression'. If i just write ' Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select Sum([Shark Individual Weight]) From FishCaught', the sum will be display. So i think there's error in WHERE clause. Any help would be appreciated, thank you.

Answer 1

Sum is a function and you have a disturbing single quote, thus:

"Select Sum([Shark Individual Weight]) From FishCaught"

Answer 2

this is your code

Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select sum[Shark Individual Weight] from FishCaught'", myConnection)
Dim ds As DataTable = New DataTable()
da.Fill(ds)
DataGridView1.DataSource = ds

myConnection.Close()

Supposed to be the code must be like this

Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select sum(The_Name_Of_The_Field) from FishCaught'", myConnection)
Dim ds As DataTable = New DataTable()
da.Fill(ds)
DataGridView1.DataSource = ds

myConnection.Close()

Is that so, try this FINAL

 Dim da As OleDbDataAdapter = New OleDbDataAdapter("Select sum(Shark__Individual_Weight) as FishWeight from FishCaught'", myConnection)
    Dim ds As DataTable = New DataTable()
    da.Fill(ds)
    DataGridView1.DataSource = ds

    myConnection.Close()

Try to change Shark Individual Weight to Shark_Individual_Weight it will work

Answer 3

"Select Sum([field name]) From FishCaught" should work. Have you checked the field name in database, it should not contain any space. To make field name more readable you can replace any spaces with "_".