mismatch type.... ADO,VB,ExcelDB (resolved)

i know that this problem occurs to many of us....(base on the forums i seen)...but i still cant figure out why ...i got this error...due to the sql statement...:(
....basically i was trying to link up with the excel db....from vb6.....which did successfully..and when i use select * [xxx$]....it works too..but when i add addition condition ( ie.select....WHERE [xx... ] i got an error saying data type mismatch for criteria expression...

Goh, are you making sure you're using quotes for text fields or non for numeric? check the cell format.

is it possible to further elaborate?...:)..i check the format cells...and it was under the 'general' category..

Sure Goh, if I get you right, you're passing a value to your recordsource to let it know what record you want. If this value is numeric, you might want to try formatting the columns as such and when setting the recourdsource do not enclose the value in single quotes. ex: rs.source = & NumericValue. If the value you're passing is string, set the cells as text, then use rs.source=' " & TextValue & " ' " . HOpe this works.

hmm...thanks for the reply..:)..but the textbox created in my form...actually has no concern to the codes below that has the problem..
the statment :

VB Code:

objRS.Open "Select * from [UserInfo$] where [Name] = 'ffff'", objconn, adOpenDynamic, adLockOptimistic, adCmdText

... merely trying to extract the recordset and i want to test it on the msgbox as shown.. but i cant even do that....:( let alone be the text1.text ...hope you understand..

The only other thing I can think of that you might wanna ckeck is the driver. But a mismatch error? If you are able to pull all records, but can't pull one at a time, gotta be something in your sql string or the field properties.

You should "Google" the phrase "data type mismatch for criteria expression"

I just did it and it game me a lot of ideas as to what the problem might be.


example:
http://www.xlinesoft.com/asprunner/d...expression.htm

"MS Access is characterized by strong data typing; it imposes a rigorous set of rules on given column values."



I know that you're not using an actual MS Access database, but that should point your brain in the right troubleshooting direction.

Just a guess, but considering what the error points to, try changing the cell type to "text" for the cells in the "Name" column.

The first name is a number. Since the cell type is "general" Excel can choose what to think of it as and maybe it's considering the cell with the number in it as a number type.

I can't get your code to work to try it. I'm probably using the wrong reference for the ADO.

h thanks for the reply...btw the reference is Microsoft ActiveX Data object 2.7 library...:)

hey...i found that if i replace ffff with 123 in the sql statement it works...but i still cant use text...