Create a search using multi select listbox and other criteria

[mokep]

I'm creating a form that will look at records of properties and filter these records to match parameters chosen by the user. This is to help with market comps. For example, if I want all Medical Offices in Atlanta built in 2005, I select Atlanta under the City box, 2005 in the Year Built box, and Medical Offices under the Property Type box. I have made a simple form that does this. I used a query and typed

Is Null Or Like "*" & [Forms]![frm projects]![TxtCity] & "*"

in each box's Criteria section (changing the TxtCity to TxtYearBuilt, TxtPropertyTYpe, etc.)

I've also figured out how to create a list box on the form that allows the user to select more than one type of property. However, I can no longer run the query now. It will neither search what is selected from the listbox, nor will it search anything selected in the other boxes (i.c. city, year built, etc.)

Is there something I can write in the Property Type's query Criteria box. Or is there something to type in the coding section of the Run Query button?

 

[Minty]

Have a look here for a well documented search form. http://allenbrowne.com/ser-62.html

 

[mokep]

Have a look here for a well documented search form.


Thank you so much!

 

[mokep]

The given link does not really talk about multi-select listboxes. Is it even possible to create a search with the user selecting more than one option in a list?

 

[MarkK]

You need to write a loop that traverses the selected list items--check out the Listbox.ItemsSelected property--and builds the criteria to use as a WHERE clause or filter.
hth
Mark

 

[mokep]

Thank you Markk. Where would you write this code, and how exactly would this code look?

 

[MarkK]

Here is an example of how to loop thru the ItemsSelected property of a Listbox, and construct query criteria or a filter based on what is selected...

    Dim var
    Dim tmp As String
    
[COLOR="Green"]    'loop thru each selected item[/COLOR]
    For Each var In Me.YourList.ItemsSelected
[COLOR="Green"]        'concatenate criteria for each selected item[/COLOR]
        tmp = tmp & "OR YourField Like '*" & Me.YourList.Column(0, var) & "*' "
    Next

[COLOR="Green"]    'if there is a result, add "WHERE" and drop the leading "OR "[/COLOR]
    If Len(tmp) then Debug.Print "WHERE " & Mid(tmp, 4)

Because the number of elements that may be selected in the list is variable, therefore I think you are going to need to construct the entire query programmatically. I don't see how you can pre-write SQL that will read a variable number of criteria.
hth
Mark