Inserting into Access Yes/No Field [Resolved]

[rabaile]

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.

[The_Duck]

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 & ')"

[rabaile]

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?

[tr333]

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

[jmcilhinney]

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.

[rabaile]

Taking the quotes off fixes it. Thanks so much!