Inserting into Access Yes/No Field [Resolved]

I'm trying to insert a new row into a table and one of the fields I want to insert into is a Yes/No. I've tried inserting "Yes", "True", "Y". I get datatype mismatch each time. If I take it out of the statement, it works fine, so I know that is the problem field.

Code:
activeswitch = "True"

Dim projectInsertFormat As String = "INSERT INTO project (CCN, ProjectName, Active, ActiveDate) " & _
"VALUES('{0}', '{1}', '{2}', '{3}')"
Dim projectInsert As String
projectInsert = String.Format(projectInsertFormat, _
txtccn.Text, _
txtproject.Text, _
activeswitch, _
datehold)

Active is the Yes/No Field in the DB. How should I recode this insert statement? Should the variable be string? Thanks.

When using SQL there is no such datatype as Yes | No

It is in fact a Boolean field - of type True | False. Thus Your statement will look something like

"INSERT INTO project (CCN, ProjectName, Active, ActiveDate) " & _
"VALUES('0', 'TestProject', 'True', '" & Date.Now & ')"

Dim projectInsertFormat As String = "INSERT INTO project (CCN, ProjectName, Active, ActiveDate) " & _
"VALUES('{0}', '{1}', 'True', '{2}')"
Dim projectInsert As String
projectInsert = String.Format(projectInsertFormat, _
txtccn.Text, _
txtproject.Text, _
datehold)

This would be the same as your statement, just in my format. Still get datatype mismatch. Ideas?

you need to change the txtproject.Text to CType(txtProject.Text, Boolean) for it to be passed as a boolean value, not a string.

You do not put single quotes around the True or False value. I don't know about other databases but I use Access almost exclusively and I never put single quotes around a boolean value when using it in an explicit SQL statement. Single quotes are for strings only.

Taking the quotes off fixes it. Thanks so much!