Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Criteria Expression Error - HELP!

Status
Not open for further replies.

ShotoCon

Technical User
Apr 27, 2008
14
US
Hi There,

Have this bit of code here:

Code:
dbQuery1 = "SELECT key FROM tempWin"
   Set recordSet = dbConnection.execute(dbQuery1)

   Dim winKey

   winKey=Cint(recordSet("key"))

   dbQuery1 = "SELECT * FROM contestants WHERE key = '"&winKey&"'"
   Set recordSet = dbConnection.execute(dbQuery1)

which seems to be producing the following error in conjuection with the last recordSet execute statement (have included the previous statement so you know where im getting the data from):

Code:
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC Microsoft Access Driver] Data type mismatch in criteria expression.


The 'key' field in the 'contestants' table is an AutoNumber and the 'key' field in the 'tempWin' table is a number. I know that when variables are created in ASP they are not assigned a variable type (Dim winKey), so when this error first started being thrown, i cast it as an integer as seen in code above.

Have even tried setting the last dbQuery1 to the following:

Code:
SELECT * FROM contestants WHERE key = '"&recordSet("key")&"'

This had the same error.

Help anyone, please?

Cheers

ShotoCon
 
I think key is a reserved word in Access. As such, I would suggest you change the name of that column (if you can). If you cannot change the name, then you should use [square brackets] around the column name, like this...

[tt][blue]dbQuery1 = "SELECT * FROM contestants WHERE [!][[/!]key[!]][/!] = '"&winKey&"'"[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Umm, can ignore this - figured it out.

Need to remove the enclosing ' ' from the winKey statement so the new code looks summit like this:

Code:
"SELECT * FROM contestants WHERE key = "&winKey&""
 
actually you don't need the ending "" or "'" since you don't wrap those around numbers.
"SELECT * FROM contestants WHERE key = " & winKey
the above should be sufficient
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top