Multiple Criteria Search HELP!

[eriel3]

I am trying to create a search form in Access that you can put several in criteria to return information.

I created the form with unbound fields:

txtArea
txtSubarea
txtType
txtEquipment

Then I set-up the query to say the following for each field
[forms]![search]![txtArea] Or [forms]![search]![txtArea]Is Null.
But when I do this it works for the first two fields but, when I put in criteria for all of the fields it returns everything. I want it to filter down by area, then subarea, then type, and so on. What am I doing wrong?

 

[lespaul]

What does your SQL look like?

Leslie

 

[OhioSteve]

This is a possible solution:

1) Do not allow nulls in the selection form's combo boxes. For example, the combo boxes could default to "not applicable" or 0.

2) Do not allow nulls in the table. Periodically,run an update query that replaces nulls with "undefined" or 0. Alternatively, you could monitor input to stop nulls.

3) Use syntax similar to this in the query's criteria

Like IIf([Forms]![Form5]![Text1]="not applicable","*",[Forms]![Form5]![Text1])

Once you are not dealing with nulls, it is a much simpler issue.

 

[eriel3]

This is a copy of the SQL

SELECT [EQUIPMENT MAINTENANCE].AREA, [EQUIPMENT MAINTENANCE].SUBAREA, [EQUIPMENT MAINTENANCE].EQUIPMENTDESCRIPTION, [EQUIPMENT MAINTENANCE].WORKORDER, [EQUIPMENT MAINTENANCE].SEQUENCE, [EQUIPMENT MAINTENANCE].HISTORY, [EQUIPMENT MAINTENANCE].DATE, [EQUIPMENT MAINTENANCE].RMTYPE, [EQUIPMENT MAINTENANCE].EQUIPMENTTYPE
FROM [EQUIPMENT MAINTENANCE]
WHERE ((([EQUIPMENT MAINTENANCE].AREA) Like IIf([Forms]![Search]![txtArea]="not applicable","*",[Forms]![Search]![txtArea])) AND (([EQUIPMENT MAINTENANCE].SUBAREA) Like IIf([Forms]![Search]![txtSubArea]="not applicable","*",[Forms]![Search]![txtSubArea])) AND (([EQUIPMENT MAINTENANCE].RMTYPE) Like IIf([Forms]![Search]![txtRMType]="not applicable","*",[Forms]![Search]![txtRMType])) AND (([EQUIPMENT MAINTENANCE].EQUIPMENTTYPE) Like IIf([Forms]![Search]![txtEquipmentType]="not applicable","*",[Forms]![Search]![txtEquipmentType]))) OR ((([EQUIPMENT MAINTENANCE].AREA)=[forms]![Search]![txtArea]));

This still does not work.

 

[lespaul]

First of all since you are not joining into any other tables you can remove all the [EQUIPMENT MAINTENANCE] from everywhere to make it easier to read.


SELECT AREA, SUBAREA, EQUIPMENTDESCRIPTION, WORKORDER, SEQUENCE, HISTORY, DATE, RMTYPE, EQUIPMENTTYPE
FROM [EQUIPMENT MAINTENANCE]
WHERE
(((AREA) Like IIf([Forms]![Search]![txtArea]="not applicable","*",[Forms]![Search]![txtArea])) AND
((SUBAREA) Like IIf([Forms]![Search]![txtSubArea]="not applicable","*",[Forms]![Search]![txtSubArea])) AND ((RMTYPE) Like IIf([Forms]![Search]![txtRMType]="not applicable","*",[Forms]![Search]![txtRMType])) AND ((EQUIPMENTTYPE) Like IIf([Forms]![Search]![txtEquipmentType]="not applicable","*",[Forms]![Search]![txtEquipmentType])))
OR (((AREA)=[forms]![Search]![txtArea]));

So you go through all your fields: AREA, SUBAREA, RMTYPE, EQUIPMENTTYPE and it gathers all the information that you want

AND THEN YOU SAY:

OR (Area)=[forms]![Search]![txtArea]));

So no matter what's in the other criteria, when you add this OR, you have added all the records where this is the ONLY condition that is met.

I think if you remove this, you should be fine.

Leslie

 

[eriel3]

thank you very much. it worked.