I feel guilty about posting again but I need help again and I have tried other sources of help e.g google and asking someone who is familiar with vb.net but still can't find the solution to it. It must be another simple fix but I cant find any reference to it online and the person I asked said that the checkbox values are -1 for false and 0 for true but when I use it in a sql statement it doesnt work. Paid is the field name for a checkbox in ms access and I would like to return the number of records for a certain student where they have paid.

The following code returns this error Conversion from string "SELECT * FROM tblBookings WHERE " to type 'Long' is not valid.

Dim con As New OleDb.OleDbConnection
        Dim OleDBCon As System.Data.OleDb.OleDbConnection
        Dim ds As New DataSet
        Dim da As System.Data.OleDb.OleDbDataAdapter
        Dim sql As String
        Dim numberofpaid As Integer
        Dim Target As Integer
        Dim Target2 As Integer
        Target = Val(txtStudentId.Text)
        Target2 = 0




        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"

        sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target And "Paid =" & Target2


        MsgBox(sql)
        OleDBCon = New System.Data.OleDb.OleDbConnection(con.ConnectionString)

        da = New System.Data.OleDb.OleDbDataAdapter(sql, OleDBCon)
        OleDBCon.Open()

        da.Fill(ds, "PaidBookings")

        numberofpaid = ds.Tables("PaidBookings").Rows.Count
        MsgBox(numberofpaid)

I did have to think twice before posting as it may seem like as soon as I hit a problem I post it here but I do search around first and try out a few different things e.g using boolean values but it didn't work.

Edited 6 Years Ago by akamini: n/a

sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target And "Paid =" & Target2

The word And should be inside your quotes.

sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target & " And Paid = " & Target2

And I believe the person told you wrong on checkbox values in Access. -1 is true.

trust my luck I work on it for two hours, doesnt work, I post for help and then as soon as I do it i try changing one thing and it works.

Target2 = -1




        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = \DrivingSchool.mdb"

        sql = "SELECT * FROM tblBookings WHERE StudentID = " & Target & " And Paid =" & Target2

Thanks apegram I think I'm finally getting understanding where to put the & thanks to your help.

How could you, update the fields on database if its data type is Y/N, and the method of updating is a checkbox??

This question has already been answered. Start a new discussion instead.