SQL data mismatch error

[ozpeppers]

Hi

I'm executing an sql statement and get an error msg - "data mismatch in criteria expression" pointing to the RS.Open line

//code

mySQL = "SELECT * FROM room WHERE [roomTypeID]like'" & myRoomTypeID & "' AND roomMaxPeople >='" & myRoomMaxStu & "' ORDER BY roomMaxPeople;"

Conn.Open
RS.Open mySQL, Conn, adOpenDynamic, adLockOptimistic, adCmdUnknown

// end of code

In the access 97 database:

"roomMaxPeople" is an long integer number

The variable "myRoomMaxStu" is an set as integer

any ideas?

cheers in advance mark

 

[scorpio66]

mySQL = "SELECT * FROM room WHERE [roomTypeID]like" & myRoomTypeID & " AND roomMaxPeople >=" & myRoomMaxStu & " ORDER BY roomMaxPeople;"

Chaz

 

[scorpio66]

Rats, second attempt

mySQL = "SELECT * FROM room WHERE [roomTypeID]like " & myRoomTypeID & " AND roomMaxPeople >= " & myRoomMaxStu & " ORDER BY roomMaxPeople;"

Forgot some spaces

Chaz

 

[woyler]

You are passing you variables as strings. If myRoomMaxStu is an integer then lose the single quotes('):

mySQL = "SELECT * FROM room WHERE [roomTypeID]like" & myRoomTypeID & " AND roomMaxPeople >=" & myRoomMaxStu & " ORDER BY roomMaxPeople;"

 

[ozpeppers]

Cheers Chaz & Woyler

Goes like a dream

Mark