Solved

Error: Data type mismatch in criteria expression

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

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