ACCESS FEED: [MS_AccessPros] Re: Creating search form for multiple criteria

Rabu, 16 Juli 2014

[MS_AccessPros] Re: Creating search form for multiple criteria

John:

I apologize for the late response. I've putting out fires at work for the past couple of days. I'm going to be working on that database today so I'll do what you suggested. I'll let you know if it works or not.

Thanks for all of your help.

Karen

---In MS_Access_Professionals@yahoogroups.com, <JohnV@...> wrote :

Karen-

The best way to do this sort of "filter by form" is to build a form with unbound controls (and no Record Source) and then write code to evaluate what the user typed and build a filter. You will still need to open a form, but if you make the default view of the form Datasheet, it'll look just like the results of a query - or what you call "worksheet" view.

Do you want the user to specify that a search value entered for Activity should also look in OrgName, or do you want to do that by default? Do you want the user to specify AND or OR?

Let's say you have an unbound form with controls called txtActivity, txtOrg, txtAddress, and txtEventDate. Put a command button on the form and put something like this in the Click event:

Private Sub cmdSearch_Click()

Dim strFilter As String

' See if Activity entered

If Len(Me.txtActivity & "") > 0 Then

' Build a filter on Activity Name

strFilter = "([Activity Name] LIKE '*" & Me.txtActivity & "*')"

End If

' See if Org entered

If Len(Me.txtOrg & "") > 0 Then

' Add or build a filter on OrgName

If Len(strFilter) > 0 Then strFilter = strFilter & " AND "

strFilter = strFilter & "([OrgName] LIKE '*" & Me.txtOrg & "*')"

End If

' Check Address

If Len(Me.txtAddress & "") > 0 Then

' Add or build a filter on Address

If Len(strFilter) > 0 Then strFilter = strFilter & " AND "

strFilter = strFilter & "([Address] LIKE '*" & Me.txtAddress & "*')"

End If

' Check Event Date

If Len(Me.txtEventDate & "") > 0 Then

' Add or build a filter on Address

If Len(strFilter) > 0 Then strFilter = strFilter & " AND "

strFilter = strFilter & "([EventDate] = #" & Me.txtEventDate & "#)"

End If

' If no filter built,

If Len(Me.strFilter) = 0 Then

MsgBox "You must enter at least one filter value."

Exit Sub

End If

' Open the Datasheet form

DoCmd.OpenForm "Datasheet Form", View:=acFormDS, _

WhereCondition:=strFilter

' Close me

DoCmd.Close acForm, Me.Name

End Sub

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Jul 14, 2014, at 5:09 PM, k2j1203@... [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

I am trying to create a search form. I have 4 criteria that the user can search on: Activity Name, Org Name, Address & Event Date. I would like to have Access search by any or all of those items, based on what the user types in. For example, the user types in Ward in the OrgName and 4251 in the Address. I would like the results to include any records that Ward and 4251 in the record as well as any that have only Ward or 4251. To throw another request at you. Can I also program the search to search multiple fields for the same keyword? For example, the user types in Ward in the Activity Name search field and records with Ward in the Activity Name or Org Name shows up as the results?

I know that I can do a search by form. If I do that though, the results show up in the form view. I would like the results to display in the worksheet view.

I have no idea even where to begin.

Thanks.

Karen