LIKE Operator in VBA

preethi

Registered User.
Local time
Today, 14:43
Joined
Nov 5, 2007
Messages
30
Hi All,

I was trying to use the query given below in VBA. But its not working properly.

Dim searchwrd As String
searchwrd = InputBox("Enter the text to be searched")
str = "SELECT * FROM tblPurchaseOrder WHERE YMEOrderRef Like '" & searchwrd & "'"
rs.Open str, cn, adOpenDynamic, adLockOptimistic
Debug.Print str
If Not rs.EOF Then
MsgBox "hi"
End If
rs.Close

I entered some data that will give outputs in the query in access. But in VBA the same search data showing as rs is closed.

I hope anyone can give me a proper guidence.

Thanks & Regards

Preethi Renjith
 
Sorry for sending the same question twice. Actually my pc was showing network error while i send the questions. That's why i resend it.

I gave the wildcards in the searchwrd as

searchwrd="XSSP*"

but it is not working.

Regards

Preethi
 
I am more of a DAO person (old school ;) ) than ADO... but I dont see you setting up CN... as in your connection...

I take it you dont get the popup Hi??
 
Hi,

Thanks for your quick reply.

I have writtern code to set both the recordset(rs) & connection(cn) objects. That doesn't have a problem. Also a lot of records are existing in the database starting with "XSSP". So i put this search option to find out the correct one.

If I use the string coming in Debug window as query its showing result(Debug.Print is there in the code). But inside VBA code if we point to rs.eof its shows as true value. Actually its false.

why is it so?

Regards

Preethi
 
Perhaps you have to add the ADO variant of "movefirst" in front of the check?
 
Hi,

I didn't get your idea. If the recordset is showing no items , then what is the use of Recordset.Movefirst

Regards

Preethi
 
I have no clue... like I said... Not a ADO 'fan'

try the dao variant..

set rs = currentdb.openrecordset (str)

Presuming your data is in the same database that should work.
 
Hi

Thanks for your reply...

But this is big project. Its already up in the server. The other parts are using ADO. So its difficult for me to change from ADO to DAO.

Hope anyone else can help me.

Regards

Preethi
 

Users who are viewing this thread

Back
Top Bottom