Data type mismatch in criteria expression

Results 1 to 2 of 2

Thread: Data type mismatch in criteria expression

  1. #1
    Join Date
    Dec 1969
    Posts
    14

    Default Data type mismatch in criteria expression

    I want to create a recordset with a SQL join statement, but it keep poping up an error:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039 80040e07&#039 <BR>[Microsoft][ODBC Microsoft Access 97 Driver] Data type mismatch in criteria expression<BR><BR>&#039here is my SQL statement, similar to access SQL<BR>&#039strEmployeeID is a var that store input from the user<BR><BR>sql="SELECT Sales.SaleName, Calls.ContactID, Calls.EmployeeID<BR>FROM Sales INNER JOIN Calls ON Sales.SaleID = Calls.SaleID<BR>GROUP BY Sales.SaleName, Calls.ContactID, Calls.EmployeeID<BR>HAVING EmployeeID=&#039" & strEmployeeID & "&#039;"<BR><BR>&#039I thought the error is in my sql statement so I try to simpify &#039it into a single table query and it gave me the same error &#039message<BR><BR>SELECT Calls.SaleID FROM Calls where EmployeeID=&#039" & strEmployeeID & "&#039"<BR><BR>&#039I try the above SQLs in Access SQL and it works, but I don&#039t know why it doesn&#039t work in ASP. The only difference it the string strEmployeeID. I input the number in Access manually.<BR><BR>&#039the problem is either in my strEmployeeID which I don&#039t know &#039why or it is in my database modeling. <BR>&#039there are many calls in one sale (one to Many)<BR>&#039But the SaleID data type is autonumber in the one end and &#039number in the many end. Please help!!<BR><BR><BR>

  2. #2
    Join Date
    Dec 1969
    Posts
    2,849

    Default RE: Data type mismatch in criteria expression

    The only time you want to use single quotes between values for WHERE clauses is when the datatype is a text or datetime. Let me explain:<BR><BR>You have:<BR>SELECT Calls.SaleID FROM Calls where EmployeeID=&#039" & strEmployeeID & "&#039"<BR><BR>Note the single ticks (&#039) before EmployeeID= and after the variable. You only want those ticks there if EmployeeID is of type text or datetime, but I&#039m wagering that EmployeeID is of type Autonumber of Numeric, correct?<BR><BR>So, try this:<BR>SELECT Calls.SaleID FROM Calls where EmployeeID=" & strEmployeeID<BR><BR>that should do the trick. There is a good article on 4Guys that discusses quoting and getting all those silly quotation marks correct. Check it out...<BR>http://www.4guysfromrolla.com/webtech/013099-1.shtml<BR><BR>Have a great day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •