sql syntax question 1

[horoscope]

Private Sub cmdsearch_Click()
Dim DB As New ADODB
Set DB = CurrentProject.Connection
Dim strtj As String
strtj = " SELECT tblsupplier.SupplierID, tblsupplier.SupplierName, tblsupplier.Address, tblsupplier.Postal, tblsupplier.Tel, tblsupplier.Fax, tblsupplier.BP, tblsupplier.Email, tblsupplier.Contact, tblsupplier.message INTO tbltemp FROM tblsupplier"
strtj = strtj & "where SupplierID like '*" & txt¹©Ó¦ÉÌ_ID & "*' "
MsgBox strtj
Dim cmd As Command
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = CurrentProject.Connection
.CommandType = adCmdText
.CommandText = strtj '
End With

Dim rs As Recordset
Set rs = New ADODB.Recordset
rs.ActiveConnection = CurrentProject.Connection
rs.Open "tbltemp", , adOpenKeyset, adLockBatchOptimistic, adCmdTable
Set rs = Nothing
DoCmd.SelectObject acTable, "tbltemp", True
DoCmd.OpenTable "tbltemp", acViewNormal, acReadOnly
Set DB = Nothing
'frmsupplier_sub.Requery
End Sub

THE QUESTION IS :
USE THE UPPER CODES, I SHOULD GET THE TABLE CALLED tbltemp WHICH HAVE THE DATA THAT SUIT FOR THE CONDITION, BUT THE RESULT IS NOT AS I THINK.
IF I DELETE THE BELOW SENTENCE "strtj = strtj & "where SupplierID like '*" & txt¹©Ó¦ÉÌ_ID & "*' "
"
THE RESULT IS RIGHT.
BUT I CAN NOT GIVE CONDITION TO THIS OPERATION.
I WANT TO HOW TO DEAL WITH IT.
THANK YOU.

 

[SmallCraig]

horoscope,

Are you fair dinkum? Is this string:

txt¹©Ó¦ÉÌ_ID

really what you want in your WHERE clause? The reason I ask is that, when I even attempt to run your code, I get an unrecognised variable name.

Another issue (and this may solve your problem, if the code in your post is exactly the code in your program) - you haven't left a space between the table name in your FROM caluse and the start of the WHERE clause.

Alternatively, if you are using ADO with an Access 2000 database and everything else really is OK, try replacing the "*" (asterisks) with "%" (percent signs).

I must admit, this one has me boxed. I hope someone else can help you with a more useful post.

Best regards,
SmallCraig

 

[horoscope]

THANK YOU FOR YOUR ANSWER. I try replacing the "*" (asterisks) with "%" (percent signs).THAT 'S WORK.
"txt¹©ӦɌ_ID" IS NOT WRONG STRING, IT IS CHINESE CHARACTERS AND FOR I USE CHINESE OPERATION SYSTEM,I AM VERY SORRY TO MAKE YOUR CONFUSED.
THANK YOU AGAIN.