Multiple Criteria Searches

[bitech]

If someone could assist me with this. I am in need of a way to allow my users to search the database using multiple criteria options. For instance; if a user wants to pull a list of companies in a certain range of zip codes. I'd like for them to able to type in the textbox...something resembling..

45202* Or 45231* Or 45255*

I would like for them to be able to press Submit and when it does that, the database searches for companies that are in those particular zip codes. However, it'd be better if they were just allowed to put commas after each zip code. If you understand what I'm saying, please give me a response.

 

[RickSpr]

Filter by Form does exactly this, and is built-in.

If you need to limit the fields they use to search by, or to allow them alternate syntax such as your "separated by commas" example, you can use the Filter and ApplyFilter events to pre- and post-process the filter version of the form. Rick Sprague

 

[bitech]

Thanks for your reply. I understand what you are saying, but I need the results to save to a different table so it can appear in a search results form. Also to allow it to be printed out.

 

[TerpFan2001]

I'm thinking you could use a list box along with your text box. The user could type a single zip code into the text box. Then the user could press a command button that would add the zip code to the list box.

When the user is done entering the zip codes, you could build an SQL statement based on the contents of the list box.

Using this combination would allow you to test each zip code before it is added to the list box.

i.e.
If Len(Me.txtZipCode) <> 5 then

Msgbox &quot;Zip Code Must Be Five Characters&quot;

Else

Me.lstZipCodeList.AddItem Me.txtZipCode

End If

Its also easier to build the SQL statement by simply looping through the entries in the list box. As opposed to searching a String for Commas.

Let me know if this helps

 

[perrymans]

Use a multi-selection list box so they may select which zip's they want, and then execute your query with the list box selections as the criteria.

Sean.

 

[bitech]

You guys have come up with some great ideas. I guess I'm a little slow with some of the coding knowledge that it takes to run the kind of query that can return the results based on the list box. If you have any reference material, please let me know.

 

[TerpFan2001]

This code will take the selected items from a list box and create an SQL Statement. Once you have the SQL statement, you can use it to Open a recordset i.e. CurrentDB.OpenRecordset( sSQL, dbOpenDynaset) or place it in the RecordSource for a Report.

Dim sSQL As String
Dim i As Integer
dim iCount as Integer

'Get number of selections made
iCount = Me.lstList.ItemsSelected.Count

'If selections have been made
if iCount > 0 Then

sSQL = &quot;SELECT * FROM tblTable WHERE &quot;

'Loop through the selected items
For i = 1 To iCount

If i = 1 Then

sSQL = sSQL & &quot;fldField = '&quot; & Me.lstList.ItemData(Me.lstList.ItemsSelected(i - 1)) & &quot;'&quot;

Else

sSQL = sSQL & &quot; OR fldField = '&quot; & Me.lstList.ItemData(Me.lstList.ItemsSelected(i - 1)) & &quot;'&quot;

End If

Next i

'If no selections were made notify the user
Else

Docmd.Beep
msgbox &quot;Please Make A Selection&quot;, vbInformation

End if

 

[bitech]

Thanks for the try, but I'm still not understanding how I'd be able to search for companies depending on the Zip Codes the user selects in the ListBox.

 

[bitech]

I figured it out THANKS!