Making a variable query...

G

Goobz

Question I have is, how do I go about making a simple query, where
when the user clicks on the query, it prompts them for the information
they would like to search for, in one specific field.

This way, I don't have to go into the Design View every single time,
and change the criteria to what they are looking for.. They can simply
input it, and it will make the criteria on its own...

Thanx!
 
J

Jeff Boyce

Open the query in design view.

Under the field for which you want the user to input a search criteria, in
the row for search criteria, add a prompt, something like:

[Enter your search term]

When the query is launched, Access prompts with what you've entered between
the square brackets.

If the user might enter a partial search term, you might use something like:

Like * & [Enter your search term] & *


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
A

Arvin Meyer [MVP]

In the criteria box of that field in the query, add something like:

[Enter a value]

for the specific value, or:

Like [Enter the first part of a value] & "*"

for a partial match, or:

Like "*" & [Enter any part of a value] & "*"

for any part of a match. For a range of dates, try:

Between [Enter Start Date] And [Enter End Date]
 
A

Ananth

Is it possible to look in 2 fields a common reference. For I have 2 fields
Local Supplier Name, DUNS Supplier Name. I want to see all Suppliers which
contains "Accenture" in either of the fields

Jeff Boyce said:
Open the query in design view.

Under the field for which you want the user to input a search criteria, in
the row for search criteria, add a prompt, something like:

[Enter your search term]

When the query is launched, Access prompts with what you've entered between
the square brackets.

If the user might enter a partial search term, you might use something like:

Like * & [Enter your search term] & *


Regards

Jeff Boyce
Microsoft Office/Access MVP

Goobz said:
Question I have is, how do I go about making a simple query, where
when the user clicks on the query, it prompts them for the information
they would like to search for, in one specific field.

This way, I don't have to go into the Design View every single time,
and change the criteria to what they are looking for.. They can simply
input it, and it will make the criteria on its own...

Thanx!
Click to expand...
Click to expand...
 
J

Jeff Boyce

Ananth

If there are two fields in which a "name" might occur, you'd need to use a
prompt for each... or create a UNION query to treat the two as one... or
MUCH better still, modify your table structure. I suspect further
normalization is needed if you have two columns with "Supplier Name".
You've put data ("Local", "DUNS") into your fieldnames, a poor use of the
relational database.

Check on "normalization" to see if you could simplify the table design.
After all, if you end up with a third type of "Supplier", with your design,
wouldn't you need to add another field in the table, modify your query(s),
modify your forms, modify your reports, ...!?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Ananth said:
Is it possible to look in 2 fields a common reference. For I have 2 fields
Local Supplier Name, DUNS Supplier Name. I want to see all Suppliers which
contains "Accenture" in either of the fields

Jeff Boyce said:
Open the query in design view.

Under the field for which you want the user to input a search criteria,
in
the row for search criteria, add a prompt, something like:

[Enter your search term]

When the query is launched, Access prompts with what you've entered
between
the square brackets.

If the user might enter a partial search term, you might use something
like:

Like * & [Enter your search term] & *


Regards

Jeff Boyce
Microsoft Office/Access MVP

Goobz said:
Question I have is, how do I go about making a simple query, where
when the user clicks on the query, it prompts them for the information
they would like to search for, in one specific field.

This way, I don't have to go into the Design View every single time,
and change the criteria to what they are looking for.. They can simply
input it, and it will make the criteria on its own...

Thanx!
Click to expand...
Click to expand...
Click to expand...
 
Top