!!!!....multiple field searching in MS ACCESS....!!!!

[ksakkos 0]

I have a database with many fields. e.g field1,field2,field3,...

 

then i have some records.

 

I would like to create a little interface and basically to know how i can make a multiple field search looking for example for records like:

 

in field1 containing the text "Engineer" + in field2 the text "New York"...

 

any idea to start..??

 

;(

[kasandoro 0]

Here's how I worked it out. I created a form with unbound fields (one field per field you want to search on), and structured the query to get the criteria from "Like [Forms]![search]![Criteria1]", etc. then I created another form for the results. The query is a make-table query. You can use a macro to turn warnings off to get rid of the "X records are about to be inserted into table BLAH" dialogs. Set the record source for the results form to the new temporary table you specified for the make-table query.

 

Each time you run the search, the table is deleted and re-created, so you get no duplicate results. To leave criteria out (important!!) leave an asterisk in the field you are NOT searching on, and you can use asterisks as wildcard characters. If you need more help, or an example, I would be happy to help!

 

--Kas

 

[ross_aveling 0]

APK is on the right track (EDIT, and kasandoro who beat me to it ).

 

When you create a new query in Access the criteria for every field you wish to filter should read Like [Field 1 Value] & "*" Or Is Null. You should modify the [Field 1 Value] text for each filtered field so you can easily see which field you are supplying a value to.

 

The beauty of this is that the query will allow you to supply filters to some fields and ignore others (simply hit enter on the dialog box).

 

Here is the SQL for an example query working on a simple table (YOURTABLE) with 3 fields (FIELD1, FIELD2, FIELD3).

 

Quote:

SELECT YOURTABLE.FIELD1, YOURTABLE.FIELD2, YOURTABLE.FIELD3

FROM YOURTABLE

WHERE (((YOURTABLE.FIELD1) Like [Field 1 Value] & "*" Or (YOURTABLE.FIELD1) Is Null) AND ((YOURTABLE.FIELD2) Like [Field 2 Value] & "*" Or (YOURTABLE.FIELD2) Is Null) AND ((YOURTABLE.FIELD3) Like [Field 3 Value] & "*" Or (YOURTABLE.FIELD3) Is Null));

 

You can paste this code into the SQL view of a new query then switch back to design view to get a clearer picture of what's going on.

 

[kasandoro 0]

Here's the SQL Query I use for the software media library I designed (and still refer to from time to time)

-------------------

 

SELECT DISTINCT Item.CD_ID, Item.App_Name, Item.Version, Item.CD_Key, Item.Vendor_Name, Item.Media_Type, Item.Disk_Number, Item.Disk_Total, Item.App_notes, checkouts.Checked_Out INTO Search_results

FROM Item LEFT JOIN checkouts ON Item.CD_ID=checkouts.CD_ID

WHERE (((Item.CD_ID) Like Forms!search!cd_id & "*" ) And ((Item.App_Name) Like Forms!search!appname & "*" Or (Item.App_Name)="ISNULL" ) And ((Item.Version) Like Forms!Search!appver & "*" Or (Item.Version)="ISNULL" ) And ((Item.Vendor_Name) Like Forms!Search!Vendor & "*" Or (Item.Vendor_Name)="ISNULL" ) And ((checkouts.Checked_Out)=0 Or (checkouts.Checked_Out) Is Null));"

-------------------

 

Keep in mind that I have a separate table that tracks what media is currently checked-out, and excludes those records from this query. Just remove the join if you don't need that functionality.

 

--Kas

[kasandoro 0]

He can have mine if he wants it...minus the media inventory, of course (what would he do with the 3187 CD's and DVD's already in the database?)

 

--Kas