Search fields SQL

[June7]

If you want to provide db for analysis (follow instructions at bottom of my post) I will give it a try. I've never actually set up queries like this, not sure what you want is practical. The only possible alternative is a VBA procedure to construct criteria string.

[noobaccess]

the file seems too big for me to attach it! hahaha! i tried deleting whatever that is not needed but the file size is just too large!

[June7]

You ran compact & repair? Zipped? Still too big - option is upload to fileshare site such as box.com and post link to file.

[noobaccess]

oh! for some reason it can be uploaded now!!!

Thanks thanks!! )

[June7]

Controls used to input search criteria should NOT be bound. Otherwise, will change data in the record. There is no reason for the search form to be bound unless you want to also use it to display the filtered records. To do that, put unbound search controls in form header, bind form to the parameterized query, put controls in detail section to display data, use code to requery the form. One of the tutorials referenced earlier demonstrates this.

Now that I can fully examine the query, I see that you are not using the Search textbox as criteria for each field. You reference it only under CompanyName. Missing LIKE and wildcard for the BusinessNature criteria. The complication in this query is the PricePoint. Handle if this criteria is not input by user by using Nz function and an alternate value large enough to accommodate any value in the data:

<= Nz([Forms]![Prospect Table search].[Tag],9999)

Use the same technique for date/time criteria if you ever need it:
BETWEEN Nz([Forms]!formname.fromdate,#1/1/1900#) AND Nz([Forms]!formname.todate,#12/31/2900#)

[noobaccess]

ohh! thanks! but i have a silly question to ask you..

To do that, put unbound search controls in form header, bind form to the parameterized query, put controls in detail section to display data, use code to requery the form. One of the tutorials referenced earlier demonstrates this.

how do i do this? btw, i cannot unbound my dropdown list because the data that are suppose to be display in the dropdown list will then be gone.

so does that mean that i need to set the criteria for each field? so i have to put criteria under all the other fields? but wouldnt it clash with those criteria in the dropdown list? i tried using LIKE and Wildcard for BusinessNature but it will not show the results that i select instead it will show every other business nature.

hmm... can you explain what does the 9999 means? i tried changing the criteria that you have given for my pricepoint but it doesnt seems to work...
or would you suggest for me to do another way to search for my pricepoint?

Thank you very much!!!