By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,034 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Query with several dynamic fields

P: n/a
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

Jul 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stephan" <fr******************@yahoo.dewrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
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

Jul 18 '06 #2

P: 6
depending on the number of fields that my users can write criteria in, I use 2 approaches
1 if there are a small number of fields I make 2,3, 4 max queries and depending on wheateh the user has writen somethin in the criteria field or not execute one of them
2 if there are more options i write a procedure that generates the sql code of my query. someting like
dim sql as string
sql = "SELWCT * FROM ... WHERE "

if isnull(me.criteriafield) then
sql = sql & " [tablename].[fieldname]= "& me.criteriafield & ", "
end if
...
docmd.runsql sql

hope this helps
Jul 18 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.