Can you use one query for two or more parameter query searches?

[SusanC]

Hello everyone,
I have a form with an unbound control linked to a query. So I can put an item name in the control and the query returns all records for that one item. For example, in the query criteria I have
'Like [forms]![formname]![itemname] & '*').

Is it possible to use the same underlying query to search my records via the item name or by date. For examples,

Date Criteria in query:
IIf(IsNull([forms]![formname]![itemname]), Then Like [forms]![formname]![datename],)
So if the item name isn't entered but the date is then search by date. If the item name is entered then do not search.

Item Criteria in query
IIf(IsNull([forms]![formname]![date]), Then Like [forms]![itemname]![datename],).

Is this possible? I've tried it but the returned search remains emtpy.
any advice would be great,
thanks
Sue

 

[Rickster57]

filters

You can use several parameter filters in a single query. The if isnull statements are not necessary. Create text boxes on your form that the user can pick for filtering the data. So on your form you would put a field for date and place that value in the criteria of your query for that field using the like statement. You can have mutiple filters and if the user doesn't choose to use one the * will bring all the records. See attached for example

 

[SusanC]

hiya,
thanks for the help so far.
I've taken out everything except for the Like statement.

Item = Like [Forms]![Formname]![Itemname] & '*'
Date = Like [Forms]![Formname]![Date]

If I work with just the query (switching between views) it prompts me to fills in the above. If i leave Item empty and fill in a date it does return everything for the date. If I enter an item (first three letters) and leave date empty it returns nothing.

If I work from the form view all fields returned remain blank. I'm not sure what you meant by the * part of your message either.
thanks
sue

 

[Rickster57]

typo?

Did you include the and * in the like statement for the date as well?

 

[SusanC]

Ah! I have put that in now and it works. As long as the other field is empty which is fine.
Thank you!!!
Sue