basic text search

[boiker]

In my database, I've got records that are identified by a ordinance number, entry date, and a detail field. I'd like to create a form that will perform a text search of the [details] field. I have a basic text search that uses a query with the Like command. I'd like to allow the search to be more advanced, like a boolean search.

When the "submit" button is clicked, the following code is run:

Dim strstring As String
Dim strmodified As String

strstring = Me!textstring

If InStr(strstring, "*") = 1 Then
DoCmd.OpenForm "frmOrdinance_viewonly_detailquery" 'note, i just noticed the docmd above is repeated after the if statement

Else
strmodified = "*" + strstring + "*"
Me!textstring = strmodified

End If

DoCmd.OpenForm "frmOrdinance_viewonly_detailquery"
DoCmd.Close acForm, "frmDetailQueryOrdinance_viewonly"

The [textstring] field is inserted in a Like expression..that's why I used the wildcards. I know there is better way to do this. I just haven't done VBA or access in quite a while.

 

[TheQuestioner]

Place this code within the Submit on_click event

Dim strSearch as String, strSQL as string

'check if anything was entered
If IsNull(me.textstring) then
   'complain to user
   Exit Sub
end if

'removes any leading/trailing spaces
strSearch=trim(Me.textstring)

'replace any double/single quotes
'(as it will cause search to trip over)
strSearch=Replace(strSearch,"'","")
strSearch=Replace(strSearch,"""","")

'if it doesn't contain any wild cards
if not instr(strSearch,"*") Then
   'add them on each side
   strSearch="*" & strSearch & "*"
end if

'Open target form
DoCmd.OpenForm "frmOrdinance_viewonly_detailquery"

'set record source of form to include the search criteria
strSQL="SELECT * FROM tblSource where Details LIKE " & strSearch
Forms.Item("frmOrdinance_viewonly_detailquery").Recordsource=strSQL

Additionally, I would set the recordsource of the frmOrdinance_viewonly_detailquery form to empty in design view and save it first.

 

[boiker]

thanks for the help, but i don't see how this addresses my main issue.

The function searches the "detail" field of the record which is memo control. If I want to search for the records which contain "fence" "residential" and "height".....how do I do this?