Parameter query searching multiple fields

[mhayli]

I am very new to access, so please be gentle!

I am setting up a database which contains information on reports written by
staff members in a R&D laboratory. The fields in the table are Document
Number, Document Type, Title, Author, Date, Keyword 1, Keyword 2, Keyword 3.

What I would like to do is create a user input parameter query in which the
user enters a keyword in a dialogue box, and the searches in Keyword 1,
Keyword 2 and Keyword 3 for the user input. I know how to do this for a
single field, but I need it to search over the three fields.

If I am able to acheive this, the next level I would like to go to (if I
understand how to do it) is to have a form in which the user can enter
information in Document Type, Title, Author, Beginning Date, End Date,
Keyword fields, with corresponding records being displayed. For example, you
may want to search for reports written by John between 1/1/05 and 1/2/05 with
a keyword of "high temperature". The computer would then display these
records.

Hope this makes sense.

Thanks

 

[KARL DEWEY]

Use just one Keyword field and put all the keywords in the same field. Just
separate by a space unless you need compound keywords. If using compound
keywords the decide on a seperator.
Query using criteria like this --

Like "*" & [Enter keyword] & "*"

 

[mhayli]

Thanks for your response Karl.

I have tried using just one keyword field. However, when there were mutiple
keywords in the field (eg. Cat Dog Mouse), unless the parameter query input
was "Cat Dog Mouse", the record was not returned. I want it to be returned
whether I type Cat or Dog or Mouse.

Use just one Keyword field and put all the keywords in the same field. Just
separate by a space unless you need compound keywords. If using compound
keywords the decide on a seperator.
Query using criteria like this --

Like "*" & [Enter keyword] & "*"

I am very new to access, so please be gentle!

I am setting up a database which contains information on reports written by
staff members in a R&D laboratory. The fields in the table are Document
Number, Document Type, Title, Author, Date, Keyword 1, Keyword 2, Keyword 3.

What I would like to do is create a user input parameter query in which the
user enters a keyword in a dialogue box, and the searches in Keyword 1,
Keyword 2 and Keyword 3 for the user input. I know how to do this for a
single field, but I need it to search over the three fields.

If I am able to acheive this, the next level I would like to go to (if I
understand how to do it) is to have a form in which the user can enter
information in Document Type, Title, Author, Beginning Date, End Date,
Keyword fields, with corresponding records being displayed. For example, you
may want to search for reports written by John between 1/1/05 and 1/2/05 with
a keyword of "high temperature". The computer would then display these
records.

Hope this makes sense.

Thanks

 

[KARL DEWEY]

It will work if you use criteria like --
Like "*" & [Enter keyword] & "*"

Thanks for your response Karl.

I have tried using just one keyword field. However, when there were mutiple
keywords in the field (eg. Cat Dog Mouse), unless the parameter query input
was "Cat Dog Mouse", the record was not returned. I want it to be returned
whether I type Cat or Dog or Mouse.

Use just one Keyword field and put all the keywords in the same field. Just
separate by a space unless you need compound keywords. If using compound
keywords the decide on a seperator.
Query using criteria like this --

Like "*" & [Enter keyword] & "*"

I am very new to access, so please be gentle!

I am setting up a database which contains information on reports written by
staff members in a R&D laboratory. The fields in the table are Document
Number, Document Type, Title, Author, Date, Keyword 1, Keyword 2, Keyword 3.

What I would like to do is create a user input parameter query in which the
user enters a keyword in a dialogue box, and the searches in Keyword 1,
Keyword 2 and Keyword 3 for the user input. I know how to do this for a
single field, but I need it to search over the three fields.

If I am able to acheive this, the next level I would like to go to (if I
understand how to do it) is to have a form in which the user can enter
information in Document Type, Title, Author, Beginning Date, End Date,
Keyword fields, with corresponding records being displayed. For example, you
may want to search for reports written by John between 1/1/05 and 1/2/05 with
a keyword of "high temperature". The computer would then display these
records.

Hope this makes sense.

Thanks

 

[mhayli]

Thanks Karl, worked a treat.

Another question if I may.

I found some information in Access help on using a form to specify criteria
for a report (searched "form query report"). I followed this help, and it
seems to be what I want to do. However, one of the things that you need to do
is create an "OK" control and macro. For the "OK" macro, the action is
"SetValue". In action arguements, help says to enter [visible] in item,
expression = no. However, the macro does not work. Any ideas?

It will work if you use criteria like --
Like "*" & [Enter keyword] & "*"

Thanks for your response Karl.

I have tried using just one keyword field. However, when there were mutiple
keywords in the field (eg. Cat Dog Mouse), unless the parameter query input
was "Cat Dog Mouse", the record was not returned. I want it to be returned
whether I type Cat or Dog or Mouse.

Use just one Keyword field and put all the keywords in the same field. Just
separate by a space unless you need compound keywords. If using compound
keywords the decide on a seperator.
Query using criteria like this --

Like "*" & [Enter keyword] & "*"

:

I am very new to access, so please be gentle!

I am setting up a database which contains information on reports written by
staff members in a R&D laboratory. The fields in the table are Document
Number, Document Type, Title, Author, Date, Keyword 1, Keyword 2, Keyword 3.

What I would like to do is create a user input parameter query in which the
user enters a keyword in a dialogue box, and the searches in Keyword 1,
Keyword 2 and Keyword 3 for the user input. I know how to do this for a
single field, but I need it to search over the three fields.

If I am able to acheive this, the next level I would like to go to (if I
understand how to do it) is to have a form in which the user can enter
information in Document Type, Title, Author, Beginning Date, End Date,
Keyword fields, with corresponding records being displayed. For example, you
may want to search for reports written by John between 1/1/05 and 1/2/05 with
a keyword of "high temperature". The computer would then display these
records.

Hope this makes sense.

Thanks

 

[KARL DEWEY]

I assume that your macro is to run the report.

I would have criteria in query like --
[Forms]![YourForm]![YourSelectTextBox]

Just have to OK button call the macro and macro just run report. Set macro
agruments for report for Print or Preview, whichever you prefer.

Thanks Karl, worked a treat.

Another question if I may.

I found some information in Access help on using a form to specify criteria
for a report (searched "form query report"). I followed this help, and it
seems to be what I want to do. However, one of the things that you need to do
is create an "OK" control and macro. For the "OK" macro, the action is
"SetValue". In action arguements, help says to enter [visible] in item,
expression = no. However, the macro does not work. Any ideas?

It will work if you use criteria like --
Like "*" & [Enter keyword] & "*"

Thanks for your response Karl.

I have tried using just one keyword field. However, when there were mutiple
keywords in the field (eg. Cat Dog Mouse), unless the parameter query input
was "Cat Dog Mouse", the record was not returned. I want it to be returned
whether I type Cat or Dog or Mouse.

:

Use just one Keyword field and put all the keywords in the same field. Just
separate by a space unless you need compound keywords. If using compound
keywords the decide on a seperator.
Query using criteria like this --

Like "*" & [Enter keyword] & "*"

:

I am very new to access, so please be gentle!

I am setting up a database which contains information on reports written by
staff members in a R&D laboratory. The fields in the table are Document
Number, Document Type, Title, Author, Date, Keyword 1, Keyword 2, Keyword 3.

What I would like to do is create a user input parameter query in which the
user enters a keyword in a dialogue box, and the searches in Keyword 1,
Keyword 2 and Keyword 3 for the user input. I know how to do this for a
single field, but I need it to search over the three fields.

If I am able to acheive this, the next level I would like to go to (if I
understand how to do it) is to have a form in which the user can enter
information in Document Type, Title, Author, Beginning Date, End Date,
Keyword fields, with corresponding records being displayed. For example, you
may want to search for reports written by John between 1/1/05 and 1/2/05 with
a keyword of "high temperature". The computer would then display these
records.

Hope this makes sense.

Thanks