Creating Customized Search

[nebula53]

I created Database and I need to create customize search.

As you can see in the attachment I have 5 drop down list boxes

In my search I need to be able to create a search by choosing all of the boxes or three or two of them and after hitting the Search button a report will appear with the information.

All the Data base information are listed in one table called ( Header). So it’s the Source of the Search.

** Edit **
Adding the image visibly :

[Delerna]

Here is one method that might work for you

In the query that the report is bound to

SELECT theFiledList
FROM theTable
WHERE range like '%' & forms!yourSearchForm.cboRange & '%'
and worktype like '%' & forms!yourSearchForm.cboWorkType & '%'
and ContractType like '%' & forms!yourSearchForm.ContractType & '%'
 

In all the combo boxes you need to ensure that the "select" option returns an empty string as its value.

What this will mean is that when no selection is made

WHERE range like '%' & forms!yourSearchForm.cboRange & '%'

will equate to

WHERE range like '%%'

and this means

WHERE range equals anything

You will probably need to play around with the syntax of this to get it working, I posted this out of my head.
The fields need to be of string type or converted to string type for this technique to work. It might also be a bit slow if you have a lot of records

There could an easier/better way so I'll be watching with interest.

[Delerna]

Incidentally, one of the better ways, I think, is to use access as the data store and develop your forms/reports as web pages.... thats one of the reasons I don't use access much any more.

In a web page I can easily check the selections made in the combo boxes, dynamically build the query accordingly, retrieve the recordset and write the values into an HTML table, textboxes, lists or whatever.


You can also build querydefs dynamically within access but I have found it all too easy to break the database by doing so.
I am talking about access 2003 here

[NeoPa]

Delerna has posted a fundamental way of doing such searches. or filtering, dynamically. There are other ways. The filter could be built on the fly in a string, then applied to the DoCmd.OpenForm method as a WhereCriteria parameter for instance.

Bear in mind, when using Delerna's approach, that many Access databases are still set up to default to the SQL-89 standard for Jet-SQL (See ANSI Standards in String Comparisons). As such, they would use the star (*) character in place of the percent (%) which is used for ANSI-92 and beyond.

Welcome to Bytes!

[nebula53]

thank you Guys I really appreciate your help

[NeoPa]

No worries. I'm glad we were able to assist :)