Error: Data type mismatch in criteria expression

Posted on 2011-02-24
Last Modified: 2012-05-11
I am using
- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I have reviewing my code and cannot find my error:
     [Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

VERY frustrated. I thought I had it working!.

The first table after the <body> will not display. I am attaching the code.
	dim title
	title = "Complexity Levels and Library Staffing"

	'---Create recordset 

Dim sqlCity, objRSCity

sqlCity = "SELECT tblGeneral.ChartID, tblGeneral.City, tblGeneral.State, " & _
 		"tblGeneral.AllHCF, " & _
 		"tblStaff.LibManTitle " & _
 	"FROM tblGeneral LEFT OUTER JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
	"WHERE tblStaff.libManTitle IS NULL AND tblGeneral.AllHCF LIKE 'Yes' "

 		If Request.querystring("sort") = "" then
 			sqlCity = sqlCity & "ORDER BY City"
  			sqlCity = sqlCity & "ORDER BY " & Request.querystring("sort")
  		End If			
'Diagnostic for sql statement errors
'Response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"

	Set objRSCity = Server.CreateObject("ADODB.Recordset")
	objRSCity.Open sqlCity, objConn

Dim sql, objRS, s_name, s_order, s_next_order
sql = "SELECT Complexity, count(*) as ComplexityCount, " & _ 
        "round (Avg( LibFTE ),2) as AvgLibFTE, " & _

        "SUM(IIF(tblStaff.MLS = 'Yes',1,0))  as [MLS], " & _

		"SUM(IIF(tblStaff.LibManTitle LIKE '%tech%',1,0))  as [Techs] " & _

 		"FROM tblGeneral LEFT JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID " & _
        "WHERE AllHCF = 'YES' " & _
		"GROUP BY Complexity "
	s_name =  Request.querystring("sort")
	If Request.Querystring("order") = "1" Then
		s_order = "ASC"
		s_next_order = 0
		s_order = "DESC"
		s_next_order = 1
	End If
	Select Case s_name
		Case "Complexity"
			sql = sql + "ORDER BY Complexity " + s_order + " "
		Case "ComplexityCount"
			sql = sql + "ORDER BY Count(*) " + s_order + " "
		Case "AvgLibFTE"
			sql = sql + "ORDER BY Avg( LibFTE ) " + s_order + " "
		Case "MLS"
			sql = sql + "ORDER BY SUM(IIF(tblStaff.MLS = 'Yes',1,0)) " + s_order + " "
		Case "Techs"
			sql = sql + "ORDER BY SUM(IIF(tblStaff.LibManTitle LIKE '%tech%',1,0)) " + s_order + " "
	End Select

	'Set objRS = Server.CreateObject("ADODB.Recordset") 
	'objRS.Open sql, objConn 

  Set objRS = Server.CreateObject("ADODB.Recordset")
on error resume next
        objRS.Open sql, objConn

if err.number <> 0 then
  response.write err.description
end if

'Count the number of all HCF facilities
	sqlCountAllHCF = "SELECT COUNT(tblGeneral.AllHCF) AS CountAllHCF " & _
	"FROM tblGeneral " & _
	"WHERE tblGeneral.AllHCF = 'Yes' " 

	Set objCountAllHCF = Server.CreateObject("ADODB.Recordset")
	objCountAllHCF.Open sqlCountAllHCF, objConn



	<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
	<meta http-equiv="Subject" content="Library Orientation Evaluation">
	<meta http-equiv="Keywords" content="Data Collection, Department of Veterans Affairs, Evaluation Studies, Inservice Training, Libraries, Library Orientation, Library Services, Organization and Administration, Personnel Management, Questionnaires, VA Library Network, VALNET">

	<link rel=stylesheet type="text/css"

		<h2 align="left"><font face="Arial" size="4"><%=title%></font></h2>
		<p>* Indicates a consolidated facility, no separate complexity level assigned.<br>
		Includes only <i>All HCF <b>(<%Response.write (objCountAllHCF("CountAllHCF")) 
%>)</b></i> designated facilities.

		   	If (objRS.EOF) Then
			<table class="CCTable" cellspacing='0' border='1' cellpadding='5' width='650'>
				<TR style='background-color: white;'>
				<TH class="CCTable" scope='col' width='125'><font size='1' color='blue'><a href='?sort=Complexity&order=<%=s_next_order%>'><font color='blue'><b>Complexity<br>Level</b></font></a></th>
				<TH class="CCTable" scope='col' width='125'><font size='1' color='blue'><a href='?sort=ComplexityCount&order=<%=s_next_order%>'><b># of Medical<br>Centers</b></a></font></th>
				<TH class="CCTable" scope='col' width='125'><font size='1' color='blue'><a href='?sort=AvgLibFTE&order=<%=s_next_order%>'><b>Average # of<br>FTE</b></a></font></th>
				<TH class="CCTable" scope='col' width='125'><font size='1' color='blue'><a href='?sort=MLS&order=<%=s_next_order%>'><b># of MLS<br>Managed<br>Libraries</b></a></font></th>
				<TH class="CCTable" scope='col' width='125'><font size='1' color='blue'><a href='?sort=Techs&order=<%=s_next_order%>'><b># of Tech<br>Manageed<br>Libraries</b></a></font></th></TR>
			Dim r_complexity, r_complexity_count, r_avgLibFTE
		  	Do While (NOT objRS.EOF)
				r_complexity = Trim( objRS("Complexity") )
				r_complexity_count = Trim( objRS("ComplexityCount") )
				r_avgLibFTE = Trim( objRS("avgLibFTE") )
				r_MLS = Trim( objRS("MLS") )
				r_Techs = Trim( objRS("Techs") )             
					<td class="CCTable"><%=r_complexity%>&nbsp;</td>
					<td class="CCTable" align="center"><%=r_complexity_count%>&nbsp;</td>
					<td class="CCTable" align="center"><%=r_avgLibFTE%>&nbsp;</td>
					<td class="CCTable" align="center"><%=r_MLS%></td>
					<td class="CCTable" align="center"><%=r_Techs%></td>
				'---MOVE TO NEXT ROW   
		 	End If
			'---CLEAN UP

			Set objRS= Nothing

<p>The 5th column, in the chart above, is pulling the count where
the text string TECH has been entered in the LibMan field. </p>

<p>The following is a list of sites who have NOT completed the Comparison Chart update, specifically<br>
the staffing data. These sites have NO data appearing in the LibManTitle field. The MLS field for all <br>
sites has been manually populated.

'Modification of 4Guys code at

if objRSCity.eof then
  response.write("No records.")
  dim Columns, i
  Columns = 4					'number of columns you want to display
  Response.write "<table border=1 width=80% cellpadding=6px><tr>"
  i = 1
  do until objRSCity.eof
    Response.Write "<td class='CCTable' width='20%'>" & objRSCity("City") & VbCrLF
    Response.Write "</td>"
    if i mod columns = 0 then
    	'If counter divided by number of columns = 0 i.e. there is no remainder,
    	'then end the row and begin new 
    	response.write "</tr><tr>"
    end if		
    i = i + 1
  'Add extra table cells
  i = (i - 1) mod columns
    Do until i = Columns
    Response.write "<td>&nbsp;</td>"
    i = i + 1
  Response.write "</tr></table>"
end if



Open in new window

Question by:JLohman
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
ID: 34976662
Without knowing your Access field types it's hard to say, but in general you delimit them like this:

Text value: SELECT * FROM MyTable WHERE MyTextField='" & YourValue & "'"

DAte value: SELECT * FROM MyTable WHERE MyTextField=#" & YourValue & "#"

Numeric value: SELECT * FROM MyTable WHERE MyTextField=" & YourValue

LVL 30

Assisted Solution

by:Wayne Barron
Wayne Barron earned 100 total points
ID: 34976706
What line number?
LVL 10

Expert Comment

by:John Claes
ID: 34977461

A small remark ;- )
I see in the code IIF statement.
This is a Access statement and not suported in MS SQL
So the Database should be Access

But Can you tell me what DataType the fields AllHCF and LibFTE   have upon the Access Database?
Following the Code it fails upon the complexity Query
The error tells us that something is going wrong with 1 of the Criteria ==>
What are the Criteria in you're Query

SELECT Complexity,
count(*) as ComplexityCount,
round (Avg( LibFTE ),2) as AvgLibFTE,
SUM(IIF(tblStaff.MLS = 'Yes',1,0))  as [MLS],
SUM(IIF(tblStaff.LibManTitle LIKE '%tech%',1,0))  as [Techs]

FROM tblGeneral
LEFT JOIN tblStaff ON tblGeneral.ChartID= tblStaff.ChartID

GROUP BY Complexity

ORDER BY Complexity

ORDER BY Count(*)


ORDER BY SUM(IIF(tblStaff.MLS = 'Yes',1,0))

ORDER BY SUM(IIF(tblStaff.LibManTitle LIKE '%tech%',1,0))


poor beggar

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question