Solved

Error: Data type mismatch in criteria expression

Posted on 2011-02-24
3
758 Views
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"
 		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
Comment
Question by:JLohman
3 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
Comment Utility
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
 
LVL 30

Assisted Solution

by:Wayne Barron
Wayne Barron earned 100 total points
Comment Utility
What line number?
0
 
LVL 10

Expert Comment

by:John Claes
Comment Utility

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now