Form criteria to include and exclude results

[cpclifford]

I have a form that allows users to enter search criteria in two item description boxes. One box is criteria to search for and include results while the other asks for criteria to exclude. Both of these boxes make use of wild cards "*" for user ease. For instance, I want a user to be able search for and include "battery" but exclude "aaa". Currently, the query works if the exclude box is populated. However, if it is not populated, access excludes all results because of the wildcard. Is there a way to tell access to search based on the include/ exclude criteria if populated but if the exclude criteria is blank (null) to search only on the include criteria? I have attempted to write an iif statement but cant seem to get it to work.

I am currently using the statement below, but it is returning zero results if the exclude box is left blank due to the wildcard. Wildcards are required.

Like ("*" & [Forms]![FRM_SEARCH_DB]![SEARCH_ITEM] & "*") And Not Like ("*" & [FORMS]![FRM_SEARCH_DB]![SEARCH_EXCLUDE] & "*")

Any suggestions? Thanks in advance, Chris

 

[revans]

Behind your search button set the form's filter like this

If Not IsNull(Me!Search_Exclude) then
  me.filter="Like ('*' & Me![SEARCH_ITEM] & '*') And Not Like ('*' & Me![SEARCH_EXCLUDE] & '*')"
else
  me.filter="Like ('*' & Me![SEARCH_ITEM] & '*')"
end if
me.allowfilters=true

Or something to that effect.

hth,

Rich

 

[cpclifford]

Rich- Thanks for the response. I am fairly new to Access and forms and not familiar with code at all. Is there a way I can set the filter in Design View? Possibly on the Data Property Sheet? I don't fully understand what you mean by "behind your search button"

Thanks, Chris

 

[revans]

ah, you can't really dynamically set a property without code. I assume you have a button that the user clicks to find the records? Set this button's On Click property to [Event Procedure] then click the elipsis (...) to open the code pane. Access should create an On Click event stub for you. Paste the code in there so it looks like this (where Command1 is the name of your button).

Private Sub Command1_Click()
If Not IsNull(Me!Search_Exclude) then
  me.filter="Like ('*' & Me![SEARCH_ITEM] & '*') And Not Like ('*' & Me![SEARCH_EXCLUDE] & '*')"
else
  me.filter="Like ('*' & Me![SEARCH_ITEM] & '*')"
end if
me.allowfilters=true
End Sub

If you have some other method of enacting the find function other than a button let me know. We'll find a place to put this code.

hth,

Rich