Data type mismatch in criteria expression

[Captain Ru]

the Access database data typs for the 'active' and 'officer' fields are
datatype Yes/No

---------------------------------------EmTiDOHActOff.cfm
--------------------------------------------------------------------

<form action = "EmTiDOHActOff_action.cfm" method = "post">
<table>
<tr>
<td>Email Address:</td><td colspan="3"><input name = "email" type =
"text"></td>
</tr>
<tr>
<td>Title:</td><td><input type "text" name = "title"></td>
<td>Date of Hire:</td><td><input type = "text" name = "DOH"></td>
</tr>
<tr>
<td rowspan="2">Is this employee active?</td><td><input type = "radio" name
= "active" value = "Yes">Yes</td>
</tr>
<tr>
<td><input type = "radio" name = "active" value = "No">No</td>
</tr>
<tr>
<td rowspan="2">Is this employee an officer?</td><td><input type = "radio"
name = "officer" value = "Yes">Yes</td>
</tr>
<tr>
<td><input type = "radio" name = "officer" value = "No">No</td>
</tr>
</table>
<input type = "submit" name = "Save" value = "Save Record">


--------------------------------------------------------------------------------
-------------------------------------------
---------------------------------------EmTiDOHActOff_action.cfm
--------------------------------------------------------------------



<cfquery datasource = "testdevemp" name = "addinfo">
insert into emp (email, title, hiredate, active, officer)
values ('#form.email#', '#form.title#', '#form.DOH#',
'#form.active#', '#form.officer#')
</cfquery>

_-------------------------------------------------------------------------------
--------------------------------------------------
Error Executing Database Query.
[Macromedia][SequeLink JDBC Driver][ODBC Socket][Microsoft][ODBC Microsoft
Access Driver] Data type mismatch in criteria expression.

The error occurred in D:\Inetpub\wwwroot\TestDev\EmTiDOHActOff_action.cf m:
line 12

10 : insert into emp (email, title, hiredate, active, officer)
11 : values ('#form.email#', '#form.title#', '#form.DOH#',
12 : '#form.active#', '#form.officer#')
13 : </cfquery>

[mxstu]

Don't use single quotes when inserting into an Access yes/no field. By putting
single quotes around the value, the db interprets it as a "text" value and not
boolean (i.e. YES/NO, TRUE/FALSE).

--- correct way (no single quotes)
INSERT INTO yourTable ( yourYesNoField )
VALUES ( YES )

--- causes error
INSERT INTO yourTable ( yourYesNoField )
VALUES ( 'YES' )

[Mountain Lover]

yes/no values in access are stored as 0/-1 so insert a boolean value
0=no, 1=yes
do not use quotes around numeric values
insert into emp (email, title, hiredate, active, officer)
values ('#form.email#', '#form.title#', '#form.DOH#', #form.active#,
'#form.officer#')

HTH

--
Tim Carley
[url]www.recfusion.com[/url]
[email]info@NOSPAMINGrecfusion.com[/email]

[Captain Ru]

Awesome! I swear I tried that before, but that must have been before I isolated the code segment.

Much appreciated.