Search form problem

[Dee Cee]

I have searched for an answer but it seems that people are having my problem the other way round.

I have created a simple 2 criteria search form that runs a query. It works perfectly if both fields are filled in and pulls the expected results. My problem is if 1 field is filled and the other is blank it returns no results. There are no records that will have either criteria blank but the person searching may not know both criteria or may be looking to pull a report based on a single criteria.

I want the blank field to be a wildcard, so it pulls up everything associated with the filled criteria rather than the blank representing nothing.

Will I have to create separate queries for each possible search scenario? Or is there a simple way to specify that each field is a wildcard? The number of search criteria will increase soon as the plans for this db have been scaled up. Whichever way I solve this problem will need to be scalable to say 5 or 6 search criteria.

Hope you guys can help me and a solution isn't too complicated. Ive built most of it through wizards and design rather than code as I am very unfamiliar with VB code.

 

[Tiro Cupidus]

Without knowing how your search form and query are set up to work together, it's difficult to know how to help. I'm not familiar with any wizards to set up a search form.

 

[Wolff]

Something along the lines of:

Dim stDocName As String
Dim blnTrim As Boolean
Dim strWhere As String

strWhere = ""


'if SI Ref
If Not IsNull(Me.Combo45) Then
strWhere = strWhere & "tbl_SI.[SI Ref]= " & Me.Combo45 & " And "
blnTrim = True
End If

'if SI Summary
If Not IsNull(Me.SI_Summary) Then
strWhere = strWhere & "tbl_SI.[SI Summary] Like '*" & Me.SI_Summary & "*' And "
blnTrim = True
End If

If blnTrim Then
strWhere = Left(strWhere, Len(strWhere) - 5)
End If

stDocName = "frm_improv"
DoCmd.OpenForm stDocName, acNormal, , strWhere

Should work for you - just change your table, form and field names I use this code for a search form for about 20 different fields, and hasn't failed me once Obviously if looking for dates etc, then it will be slightly different, but should give you enough to work with!

 

[Dee Cee]

Thanks for the help guys but I have discovered a problem of my own making that needs to be fixed first. Lookups in tables and storing the ID rather than the data. Messed things up and I am going back through it all and rectifying my mistakes.

Tiro: I used a bunch of resources that showed me how to set up a multi-parameter query and use a form to run it. I then used a subform to view the query as it was easier to lock and stop any accidental data changes.

Wolff: I will come back to this when I have fixed my other problems.

 

[Dee Cee]

Wolff: The code is awesome. I have all my searches working like clockwork. Thanks for the help.

 

[neileg]

Don't use table level lookups. They cause no end of trouble.

 

[Dee Cee]

I realized that pretty quickly and got rid of them all. Everything is working like clockwork now. Hopefully there wont be any last minute changes, other tha that possibility all I have left to possibly do is split it up and let people run free on it.