Multi criteria SEARCH function

[Stilla]

I have a simple database for storing information about research articles.
Fields on the input form include title, author, year, journal, type,
keypoint1, keypoint2....etc.

I'd like to build some type of friendly search function from the Switchboard
form that lets the user to choose one or more criteria, maybe from 3 or 4
combo boxes to search for a specific record. Eg., name of journal +
year+author..

What is the best way to do this, and where can I learn how?

Also, is there anyway that I can build a "keyword" search or query, so that
the user can specify a key word, eg. "leadership" and a printable report can
appear with all the references that have that word in the title fields,
and/or in the "keypoint" fields?

Thanks again!

 

[Damian S]

Hi Stilla,

Yes, both are possible. Look at having a query that returns your data based
on the selections made on your form. Remember that you will need to allow
for there to be no selection (ie: handle NULL).

Once you have a query that returns your results, you can do whatever is
necessary. Place the details on a form and let the user select a record to
goto... display the results in a report... the options are limited only by
your imagination (and the capability of Access) ;-)

Hope this helps.

Damian.

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

There's a sample database to download, demonstrating how the code works.

The "Trouble-shooting and extending" section explains how to apply the
results to a report also.

 

[Krazy Darcy]

I created one late last yearfor my studio databse system that i am working on
(and using)

I had to get help from these forums with making it.

for each field you will need to test for 3 differant combinations of
"keyword" as the field you are searching in could have it as the first word,
last word or be somewhere in between.

Like "* " & [Forms]![mane of form]![name of unbound textbox] & "*"

Like "* " & [Forms]![mane of form]![name of unbound textbox]

Like [Forms]![mane of form]![name of unbound textbox] & "*"

AND

You have to test for it being empty

Create a column: with it's control header:
[Forms]![mane of form]![name of unbound textbox]

and criteria is :
Is Null

Please note the
[Forms]![mane of form]![name of unbound textbox]
is because I have a form that is the input and results screen for my search
query.
if you just had
[name of unbound textbox]
you will get asked for the input when you run the query.

I have found my query only works when running it from the form not directly
as a query (am running access 2003.
if you have more than one field that can be searched then you have to allow
for ALL possible combinations of those fields being filled out from 1 to all.
My query has 34 criteria rows

For example:

A B [geta] [getb]
-------------------------------------------------------------------------
is null is null
Starts with is null
middle of is null
end with is null
Starts with is null
middle of is null
end of is null

starts with starts with
starts with middle of
starts with end of
middle of starts with
middle of middle of
middle of emd of
end of starts with
end of middle of
end of end of

Please note I have only used two fields for this diagram. the right hand
two columns are used to test for the user leaving that input box empty.

 

[Stilla]

Thanks for the thorough reply!

I created one late last yearfor my studio databse system that i am working on
(and using)

I had to get help from these forums with making it.

for each field you will need to test for 3 differant combinations of
"keyword" as the field you are searching in could have it as the first word,
last word or be somewhere in between.

Like "* " & [Forms]![mane of form]![name of unbound textbox] & "*"

Like "* " & [Forms]![mane of form]![name of unbound textbox]

Like [Forms]![mane of form]![name of unbound textbox] & "*"

AND

You have to test for it being empty

Create a column: with it's control header:
[Forms]![mane of form]![name of unbound textbox]

and criteria is :
Is Null

Please note the
[Forms]![mane of form]![name of unbound textbox]
is because I have a form that is the input and results screen for my search
query.
if you just had
[name of unbound textbox]
you will get asked for the input when you run the query.

I have found my query only works when running it from the form not directly
as a query (am running access 2003.
if you have more than one field that can be searched then you have to allow
for ALL possible combinations of those fields being filled out from 1 to all.
My query has 34 criteria rows

For example:

A B [geta] [getb]
-------------------------------------------------------------------------
is null is null
Starts with is null
middle of is null
end with is null
Starts with is null
middle of is null
end of is null

starts with starts with
starts with middle of
starts with end of
middle of starts with
middle of middle of
middle of emd of
end of starts with
end of middle of
end of end of

Please note I have only used two fields for this diagram. the right hand
two columns are used to test for the user leaving that input box empty.

I have a simple database for storing information about research articles.
Fields on the input form include title, author, year, journal, type,
keypoint1, keypoint2....etc.

I'd like to build some type of friendly search function from the Switchboard
form that lets the user to choose one or more criteria, maybe from 3 or 4
combo boxes to search for a specific record. Eg., name of journal +
year+author..

What is the best way to do this, and where can I learn how?

Also, is there anyway that I can build a "keyword" search or query, so that
the user can specify a key word, eg. "leadership" and a printable report can
appear with all the references that have that word in the title fields,
and/or in the "keypoint" fields?

Thanks again!

 

[Stilla]

Thanks for this response!

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

There's a sample database to download, demonstrating how the code works.

The "Trouble-shooting and extending" section explains how to apply the
results to a report also.

 

[Stilla]

Thanks Damian!

Hi Stilla,

Yes, both are possible. Look at having a query that returns your data based
on the selections made on your form. Remember that you will need to allow
for there to be no selection (ie: handle NULL).

Once you have a query that returns your results, you can do whatever is
necessary. Place the details on a form and let the user select a record to
goto... display the results in a report... the options are limited only by
your imagination (and the capability of Access) ;-)

Hope this helps.

Damian.

 

[Krazy Darcy]

its ok

only problem I had was the query runs when the search form is opened and it
was returning all records so I put a jjjj in as default value on one of the
unbound text boxes. this means no records appear when opening the form. I
still need a clear all search input button on my form.

the search button is simply linked to a macro. this macro has one action -
requery.

Thanks for the thorough reply!

I created one late last yearfor my studio databse system that i am working on
(and using)

I had to get help from these forums with making it.

for each field you will need to test for 3 differant combinations of
"keyword" as the field you are searching in could have it as the first word,
last word or be somewhere in between.

Like "* " & [Forms]![mane of form]![name of unbound textbox] & "*"

Like "* " & [Forms]![mane of form]![name of unbound textbox]

Like [Forms]![mane of form]![name of unbound textbox] & "*"

AND

You have to test for it being empty

Create a column: with it's control header:
[Forms]![mane of form]![name of unbound textbox]

and criteria is :
Is Null

Please note the
[Forms]![mane of form]![name of unbound textbox]
is because I have a form that is the input and results screen for my search
query.
if you just had
[name of unbound textbox]
you will get asked for the input when you run the query.

I have found my query only works when running it from the form not directly
as a query (am running access 2003.
if you have more than one field that can be searched then you have to allow
for ALL possible combinations of those fields being filled out from 1 to all.
My query has 34 criteria rows

For example:

A B [geta] [getb]
-------------------------------------------------------------------------
is null is null
Starts with is null
middle of is null
end with is null
Starts with is null
middle of is null
end of is null

starts with starts with
starts with middle of
starts with end of
middle of starts with
middle of middle of
middle of emd of
end of starts with
end of middle of
end of end of

Please note I have only used two fields for this diagram. the right hand
two columns are used to test for the user leaving that input box empty.

I have a simple database for storing information about research articles.
Fields on the input form include title, author, year, journal, type,
keypoint1, keypoint2....etc.

I'd like to build some type of friendly search function from the Switchboard
form that lets the user to choose one or more criteria, maybe from 3 or 4
combo boxes to search for a specific record. Eg., name of journal +
year+author..

What is the best way to do this, and where can I learn how?

Also, is there anyway that I can build a "keyword" search or query, so that
the user can specify a key word, eg. "leadership" and a printable report can
appear with all the references that have that word in the title fields,
and/or in the "keypoint" fields?

Thanks again!