Help With Multi Field Search Form

[icaines9517]

I need help creating a search form in which the user has the ability to search from approximately ten different fields within one large table containing over 1,000 records. I also want this search form to allow the user to leave some fields null. One of these fields is a combo box the others are text boxes, some being date fields. I tried doing this by creating a query and using a wildcard like statement for each field, however this does not work.

I know the answer most likely includes code but I have no idea what and how to write the code to complete my task. I'm really just hoping someone can help me, I'm a novice user but I've already learned a lot and feel I can catch on if I know how to go about doing this. I have already searched the internet for a couple weeks and found different solutions but the ones I've tried so far has not worked for my needs. Any help would be appreciated.

Thanks

[ranman256]

You must use vb to figure what fields to use, THEN build the 'where' clause.
on the button click (find) event:

sub btnFind_click()
  If not isNull(txtName) then sWhere = "[name]='" & txtName & "'"
  if not isNull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
  'if not....

   If left(sWhere,4) = " and" then sWhere = mid(sWhere,5).  'Remove 1st and

'Now apply where to the SQL or filter
  me.filter = sWhere
  Me.filterOn = true
end sub

[icaines9517]

Thank you for your response. Just a couple questions to ensure I'm able to execute this correctly. So I need to put this code on the on click event of my button correct? Also, if I want the results of the user input to essentially be a query can you show me how I would go about writing the code for that?

[orange]

You can try the code available in this thread.
Read the material and example and see if it applies.

[icaines9517]

Thanks again, but one more thing.. When I tried your method for some reason it doesn't allow me to search for fields individually. (My code is below) For instance, if I search for a Status I have to put in a Position in order for it to work or else I get an error. Any idea on how to fix this?

Private Sub Command3_Click()
Dim sWhere As String


If Not IsNull(Status) Then sWhere = sWhere & "([Status] = """ & Me.Status & """) AND "
If Not IsNull(Position) Then sWhere = sWhere & " ([Position] = """ & Me.Position & """)"


If Left(sWhere, 4) = " and" Then sWhere = Mid(sWhere, 5)
Me.CandidatesResults.Form.Filter = sWhere
Me.CandidatesResults.Form.FilterOn = True
End Sub

[orange]

As I said in the post

Read the material and example and see if it applies.

[icaines9517]

Yeah I checked out the material and the example and it doesn't apply to my needs. Thanks for your help though