SQL Query problem

Data Type mismatch error with my SQL query

[Extrobe]

Hi, I have a sql query I'm having problems with

code:
strQ = "SELECT * FROM tblDECodes WHERE DeptID= '" & ideptid & '" "

I've tried all variants of ',",'" etc, but no difference. All fields are valid, and when I response.write ideptid, I get 9 , which is correct.
The error I get is
[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.

I'm guessing it's something todo with numbers vs txt?

If I do

code:
strQ = "SELECT * FROM tblDECodes WHERE DeptID=9"

It works fine.

I much appreciate your help!

[ajavaly]

yes, that is exactly the problem - if that field is defined as a number, you should not put single quotes around it in your query.

So your code would read:

strQ = "SELECT * FROM tblDECodes WHERE DeptID= " & ideptid & "

[Extrobe]

ahh, that final one worked, thanks!

(though just needed an extra " at the end)

see if you can get your head around this one. in the db, got many tables interlinked.

There are about a dozen departments, which they select on the previous page, where most of the data is entered into a table. Each department has a code - just a number from 1- 30 or something. these codes are stored in a table, along with their corrosponding 'error codes' - the codes for what errors can be wrong in that deprtment.

So when I run that sql query, I get a list of the possable error codes for that department.

now, the actual errors are stored in a different table

Table tbleDECodes
DeptID - ErrorCode
2 - 1
2 - 3
3 - 1

Table ErrorCodes
Error Code - Name
1 - Missing
2 - Extra
3 - Damage

so what I'm trying to do, is get a list of the possable error codes, determined by the deptID - which I've done. I then need to some how (I guessing here) loop this through a second sql query to get the correspong error, so I can have a form, with the error code as the value, and the corresponding error name as the name as I said, I expect it's some sort of loop - only i've never done loops before. Got any pointers or good websites I can have a ganders at?

this is what I have so far :o_

which works, but when I reponse.write strQb, I just get

code:
SELECT * FROM tblErrorCodes WHERE ErrorCode

code:
Dim strQb
StrQb = strQb & "SELECT * FROM tblErrorCodes WHERE ErrorCode ="

If objRS.BOF And objRS.EOF Then
Response.Write "No Entries Found."
Else
While Not objRS.EOF
strQb = strQb & objRS("ErrorCode") &" OR "
objRS.MoveNext
Wend
End If

strQb = Left(strQb, Len(strQb) - 2)

[mattglet]

What you need is an INNER JOIN.

SQL = "SELECT * FROM tbleDECodes INNER JOIN ErrorCodes ON tbleDECodes.ErrorCode = ErrorCodes.ErrorCode"

You can then do whatever you want. Here's [w3schools.com] a good site to get you started on JOINs