Multi-field Search

[bubba61]

I'm a newbie to Access. So please excuse my
ignorance. I have created a database for storing automotive wiring pigtails. I am trying to perform a search on the database using a basic query. I created a
simple search form with 3 combo boxes that are tied to simple tables that limit choices for the end user. The
query is using 2 tables, "Pigtail" table and a "Service Part Description" table.
I am using 2 fields from the "Pigtail" table. The first field is "Cavity" which
houses the number of cavities of the pigtail and the other field "Wire Type"


houses the type of wire terminal (gold, silver, tin etc). I am using the field
"Service Part Description" from the "Service Part Description" table.
This is what I am wanting to accomplish. I want the ability to search by any combination
of these 3 fields. If any field is left empty, all information in that field
will be considered for the search. For example, If I want to perform a search
for a 4 cavity pigtail for an Oxygen Sensor, regardless of the wire type, I would choose '4' in the "Cavity"
field and choose "Oxygen Sensor " in the "Service Part Description" field. I
would leave the "Wire Type" field blank to match all wire types that match the
other 2 field criteria. I have tried to use this simple criteria formula (Like
"*" & [Forms]![Part Description Search]![Cavity] & "*") in the query box
on each of the 3 fields (Changing the field name on each query) that I found on
internet. However it is not working. Any
ideas?

[ssanfu]

See this: (one method)
http://access.mvps.org/access/queries/qry0001.htm


Hint: try and not use spaces in object names. It will cause you headaches......
"Service Part Description" could be "ServicePartDescription". If you MUST separate the words, use the underscore character:"Service_Part_Description"

also see:http://access.mvps.org/access/tencommandments.htm

[bubba61]

Steve, Thanks for the reply and the advise. I did remove the spaces in all of my object names and changed the criteria formula, however it returns all of the data in each field. James

[ssanfu]

Would you post the SQL of the query?

[bubba61]

SELECT Pigtail.Cavity, Pigtail.WireType, ServicePartDescription.ServicePartDescription, Pigtail.[MotorcraftP/N], Pigtail.[FordP/N], Pigtail.ReplacedBy
FROM Pigtail LEFT JOIN ServicePartDescription ON Pigtail.ID = ServicePartDescription.McraftID
WHERE (((Pigtail.Cavity)=[Forms]![PartDescriptionSearch]![Cavity]) AND ((Pigtail.WireType)=[Forms]![PartDescriptionSearch]![WireType]) AND ((ServicePartDescription.ServicePartDescription)=[Forms]![PartDescriptionSearch]![ServicePartDescription])) OR (([Forms]![PartDescriptionSearch]![Cavity] Is Null) AND ([Forms]![PartDescriptionSearch]![WireType] Is Null) AND ([Forms]![PartDescriptionSearch]![ServicePartDescription] Is Null)) OR (((Pigtail.ReplacedBy) Is Null));

[ssanfu]

Attached is an example mdb (A2000 format). I got tired of trying to get the Is Null to work.
So I went the code route.

Also, I changed 3 field names - ID to Pigtail_ID, [MotorcraftP/N] to MotorcraftP_N and [FordP/N] to FordP_N
Remember - no special characters.

When I created the tables, I made the fields "Cavity", "WireType", "MotorcraftP_N" and "FordP_N" all text types. If "Cavity" is a number type, the code will have to be changed.

Will this work for you??

[bubba61]

Steve,
Looks great! I think it will work just fine. Cavity is a number field. Can I change the code? Sorry for my ignorance, how do I incorporate it to my database?
James

[ssanfu]

Can I change the code?

Sure. Change the field type for Cavity to Integer. In the code, remove the single quotes in the cavity test.
I have done this in the attached db.....

Sorry for my ignorance, how do I incorporate it to my database?

Your table names/ field names would have to exactly match my example db. Import the query and the form.

The table is bound to the query.
The query is linked to the tables.

[bubba61]

Steve, Thanks buddy! I'll play around with it this weekend. James

[June7]

Steve, Pigtail and ServicePartDescription tables are joined on autonumber PK fields. That doesn't make sense to me.

[ssanfu]

@June, You are correct. (My bad ) I created the tables from the posted SQL and didn't look at the join.

@bubba61, Would you describe how the two tables are related? Or, if you would, post a zipped copy of your database? Just a few records, compact & repair, then zip it...


Edit: modified the db - 1 to many join

[bubba61]

Steve, June, The pigtail table contains all the info for the wiring pigtail such as motorcraft part number, ford part number, cavity, wire type etc. and the servicepartdescription table contains the related part description that it matches. I originally had a lot of this information in Excel spreadsheets which may have contributed to problem when I imported. I had originally just tried to link the motorcraft part number field, however it is a text field. Maybe I should have converted to a number field. Thanks for all the help and BTW I was successful in incorporating your search form. How do I attach the database? When I click on the attachments link, all I get is a empty box? James

[June7]

You opened the attachment manager? Do you see an Add Files button at upper right? Browse to file and upload.

[bubba61]

Had to use my laptop. For some reason it wouldn't work on my desktop.

[bubba61]

Here are a couple of pictures to see how the picture function works.