Using Access Form as a Search Tool 1

[onegurl]

I have a table made by a query that consolidates various fields from linked SQL tables.

I need to provide a user friendly search/filter to provide users with records based on any of the following fields within the table: magazine name, date, account name or acct id or any combination of these fields.

My first attempt used find record which was ok for one set of criterion. However, this just was not user friendly enough.

I used the query grid/SQL select ** where field criterion = Like "*" & [Forms]![formname]![textbox] & "*"; staggering the code from each OR line of the query grid (and of course coding the proper formname and textbox) - but I cannot get the OR portion to work properly. The query returns all the values in the table. If I remove 3 of the "Likes" and run the query with only one code of criteria, it works like a dream.

I know I'm close. But I can't quite figure what I'm messing.

Please help me see what is probably right in front of me.

And as usual, thanks for all your help - this place keeps me sane.

 

[jflachman]

Having seen this question a couple of times, I created a FAQ on this subject. Since it takes some time to get posted, I'll repost it here for now:
Let's say you want to make form called FilterForm to filter data on a form called DataForm.
You want to be able to filter DataForm for the following fields:
[Start Date] between two specified dates
[Finish Date] after a specified date
[User ID] equal to a specified user ID

You would make your FilterForm have four fields:
[StartRange1]
[StartRange2]
[FinishAfter]
[User ID Number]
All of these fields would be unbound
Also on your FilterForm you would have a button called Apply Filter

The following code would be attached to that button:

dim strFilter as String 'Used to build a filter

strFilter = "" 'Start with a blank filter
'Verify the both ends of the start date range are valid
If (IsDate(Me![StartRage1]) && IsDate(Me![StartRange2])) then 
   'Build the filter
   strFilter = "([Start Date] between #" _ 
      & Me![StartRange1] &  "# AND #" _
      & Me![StartRange2] & "#)"

'Verify the finish after date is valid
If (IsDate(Me![FinishAfter]) Then
   'If the filter is not blank, then AND this criteria  
   If (Len(strFilter) > 0)
      strFilter = strFilter + " AND "
   End If
   'Add this criteria
   strFilter = strFilter & ([Finish Date] > #" _
      & Me![FinishAfter] & "#)"
End If

'Verify the user id number is not blank (do more validation if required)   
If (Not IsNull(Me![User ID Number])) Then
   'If the filter is not blank, then AND this criteria  
   If (Len(strFilter) > 0)
      strFilter = strFilter + " AND "
   End If
   'Add this criteria
   strFilter = strFilter & ([User ID] = " _
      & Str(me![User ID Number]) & ")"
End If   

If Len(strFilter) Then
   [Forms]![DataForm].Filter = strFilter
   [Forms]![DataForm].FilterOn = True
End If

Obviously these fields and button could be on the original form. When I've used this in the past, I use a checkbox on the original form called Display Filter When the checkbox is checked, the FilterForm is opened, when unchecked it is closed. This way the filter fields are not cluttering up the form when not in use. There are lots of variations on this type of theme. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[onegurl]

Thanks for showing me the way! My filter is based on ands and ors. I'll be playing with this over the wknd. Okay if I contact you to work out the bugs?

 

[jflachman]

Yes, but I won't be around this weekend, so it will have to wait until Monday. Jonathan
________________________________________
It is not fair to ask of others what you are unwilling to do yourself.
-Eleanor Roosevelt

 

[onegurl]

I wouldn't dreaming of bothering you on a wknd - I'm just thankful you are willing to help. Have a great one and I'll be in touch next week!

 

[onegurl]

Jonathan,
Just wanted to say thanks again for your help. Your code gave me a great jumping off point and I was able to accomplish what I set out to. Now on to the next....

Thanks again,
Onegurl