Solved

Error: Data type mismatch in criteria expression

Posted on 2011-02-24
3
765 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
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




0
 
LVL 30

Assisted Solution

by:Wayne Barron
Wayne Barron earned 100 total points
ID: 34976706
What line number?
0
 
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

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

860 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