[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 775
  • Last Modified:

Error: Data type mismatch in criteria expression

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"
 		Else
  			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
	Else
		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




%>


<html>

<head>
	<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">
	<title><%=title%></title>

	<link rel=stylesheet type="text/css"
	href="/VALNET/CodeReuse/style.css">
</head>


<body>
	
		<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.
		</p>


		<%
		   	If (objRS.EOF) Then
		   		'---NO ROWS TO DISPLAY
		   	Else
		   		'---DISPLAY ROWS IN A HTML FORMATTED TABLE
		%>
			<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") )             
		                                         
		%>
				<tr>
					<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>
				</tr>
		<%                                      
				'---MOVE TO NEXT ROW   
				objRS.MoveNext()
			Loop  
		%>
		        </table>
		<%                              
		 	End If
		       
		         
			'---CLEAN UP

			objRS.Close
			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.
</p>





<%
'Modification of 4Guys code at http://www.4guysfromrolla.com/webtech/102299-1.shtml

if objRSCity.eof then
  response.write("No records.")
else
  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
    objRSCity.movenext
  loop
  'Add extra table cells
  i = (i - 1) mod columns
    Do until i = Columns
    Response.write "<td>&nbsp;</td>"
    i = i + 1
  Loop
  Response.write "</tr></table>"
  objRSCity.MoveFirst
end if

%>

</body>
</html>

Open in new window

0
JLohman
Asked:
JLohman
2 Solutions
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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




0
 
Wayne BarronCommented:
What line number?
0
 
John ClaesCommented:

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

WHERE AllHCF = 'YES'
GROUP BY Complexity

ORDER BY Complexity

ORDER BY Count(*)

ORDER BY Avg( LibFTE )

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

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


regards

poor beggar
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now