KEYWORD SEARCHING

[gobble]

I have a text box which I wish to be able to search using a keyword. However when I run the search it finds no records when I know there is 2. If I run it like a query it works however I want to do it in VB Code. I have the following code example:

CSearch = CSearch + "AimDescription Like '* " _
+ mmAimDescription + " *'"

Can anyone tell me where I am going wrong?

 

[wonderhands]

use '&' not '+'

 

[gobble]

+ and & do the same job. I change them to & but it made no difference! Any other suggestions?

 

[Stickarm]

What does the SQL Script behind the query look like? That should help you write the VB Code.

 

[gobble]

I copied what I had in the VB and pasted into the SQL and it worked fine there. So I gathered that I had written it correctly. But it just won't do it when I run through it!!

 

[Stickarm]

Try this...

CSearch = CSearch + "AimDescription like """ & _
("*" & mmAimDescription& "*&quot & """"

 

[allanon]

This is how I would do this:

CSearch = CSearch + "AimDescription like " & chr(34) & _
"*" & mmAimDescription & "*" & chr(34)

 

[gobble]

Thanks for the replies. I'm afraid neither of those worked either. It didn't like the brackets around the solution from Stickarm, and it returned the same answer as I was receiving from the solution from Allanon.

thanks anyway!!

 

[petermeachem]

If you tell us what you are doing with CSearch both before and after this statement, we might be able to help. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm

 

[gobble]

Okay here goes

I have a search form based on a my problems form. I have a command button which you click when you have selected or filled in the boxes you wish to search on. The code behind this button is....Private Sub cmdSearch_Click()
Dim CSearch, cFilter As String
Dim rsAims As ADODB.Recordset
Dim csql As String


cFilter = ""
CSearch = ""

If Me.txtAimID <> &quot;&quot; Then
CSearch = CSearch + &quot;AimID='&quot; + txtAimID.Text + &quot;'&quot;
End If

If Me.cboAimType <> &quot;&quot; Then

If Len(CSearch) > 0 Then
CSearch = CSearch + &quot; and &quot;
End If

CSearch = CSearch + &quot;AimType='&quot; + cboAimType + &quot;'&quot;

End If

If Me.mmAimDescription <> &quot;&quot; Then

If Len(CSearch) > 0 Then
CSearch = CSearch + &quot; and &quot;
End If

CSearch = CSearch + &quot;AimDescription Like '* &quot; _
& mmAimDescription & &quot; *'&quot;

End If

Set rsAims = New ADODB.Recordset

csql = &quot;select AimID from qryProblemActions where &quot; + _ CSearch

rsAims.Open csql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

If rsAims.RecordCount > 0 Then rsAims.MoveFirst

If rsAims.RecordCount = 0 Then
MsgBox &quot;No records met your criteria&quot;
Else

Do While Not rsAims.EOF

If Len(cFilter) > 0 Then
cFilter = cFilter + &quot; or &quot;
End If

cFilter = cFilter + &quot;AimID =&quot; + Str(rsAims!AimID)
rsAims.MoveNext

Loop

DoCmd.OpenForm &quot;frmProblems&quot;, acNormal, , cFilter


End If

End Sub


While stepping through the code and hovering over the values it looks correct but it does not bring any records through although I know there is two. Can anyone see what I am doing wrong?

Thanks.... a desperate user

 

[petermeachem]

Looks perfectly ok. What I would do at this point is to stop the code at rsAims.Open get the csql text in the debug window, paste it into a query in Access (assuming that is what you are using)and run it. I generally discover at this point that I have done something really stupid and obvious. Peter Meachem
peter@accuflight.com

Support Joanna's Bikeathon

http://www.accuflight.com/blue_peter_bikeathon_2002.htm