Query with several dynamic fields

[Stephan]

Hi all,

I am looking for help regarding my query.

I have a table containing different information about meetings such as
date, topic, and 3 separate keywords assigned.
Now, I would like to create a search form (and the underlying query) so

that the user can search for any information.
E.G he could limit the date range by providing a start or end date, or
provide phrases of the topic and / or one or more of the keyword
fields.
If any field is left blank, the query should run through the whole set
of records.

Any hint is greatly appreciated.

Thanks,
Stephan

 

[Allen Browne]

It would be possible to create a query with a WHERE clause like this:

WHERE ((([Forms]![Form1]![txtStartDate] Is Null)
OR ([MeetingDate] >= [Forms]![Form1]![txtStartDate]))
AND (([Forms]![Form1]![txtEndDate] Is Null)
OR ([MeetingDate] < [Forms]![Form1]![txtEndDate]+1))
AND (([Forms]![Form1]![txtKeyword] Is Null)
OR ([Keyword1] = [Forms]![Form1]![txtKeyword])
OR ([Keyword2] = [Forms]![Form1]![txtKeyword])
OR ([Keyword3] = [Forms]![Form1]![txtKeyword])))

There might be better ideas, such as creating a related table of the
keywords, so each record can be associated with whatever keywords are neeed.

You could also avoid the inefficiency of the unused criteria with approach
suggested in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html