Data type mismatch in criteria expression... 3

[OrthoDocSoft]

I've got a problem. I am trying to open a recordset from an Access database (2002 format) after creating a SQL statement, but I get an error:

"Data type missmatch in criteria expression..."

in the .open line below.

What I am trying to do is:

1) send a validation code (strSubmittedValidationCode), client ID # (intClientIDSentIn) and Session ID # (intAsID) to a subroutine,

2) find all records where these three numbers exist in one record (there should at most be only one), and

3) tell me if it exists or not.

The 'Clients' table has:
a "ValCode" field (as text), and,
a "ClientIDSentIn" field (as long integer)

The 'AsComp' table has:

an "AsIDSentIn" field (as long integer).

Here is my SQL string:

strSQL = "SELECT * FROM Clients, AsComp" _
& " WHERE ValCode = '" & strSubmittedValidationCode & "' AND ClientIDSentIn = '" & intClientIDSentIn & "'" _
& " AND AsIDSentIn = '" & intAsIDSentIn & "'"

(ignore the absent " _" in line two above -- it works fine)

Here is my Recordset open line:

objRSCheckForClientDups.Open (strSQL), objDBConnection, adOpenKeyset, adLockOptimistic, adCmdText

And, of course, to reiterate, I get the error:

"Data type mismatch in criteria expression...."

Any suggestions?

Thanks,

Ortho

 

[RoyVidar]

You only use single quotes on text fields, numeric fields, no delimiter:

[tt]strSQL = "SELECT * FROM Clients, AsComp" _
& " WHERE ValCode = '" & strSubmittedValidationCode & "' AND ClientIDSentIn = " & intClientIDSentIn _
& " AND AsIDSentIn = " & intAsIDSentIn [/tt]

Roy-Vidar

 

[OrthoDocSoft]

Star for you.

As you can tell, I have tried to write maybe a half dozen sql commands.

Over and over I can't get over how GREAT this board is and the people in it.

ortho