user input to a query

[joemyre]

I am very new to access having only used MySQL in the past but access is
not very new at the company i work for seeing that we are still using
access97. I have figured out that i can use [] to require user input to
search for a an item in a table via query, but what would be very useful
is if i could give the user that option under every column and let them
select which column they want to search under. Right now i can only let
them search by one column per query, resulting in one query per search
item. That leaves me with about 8 queries. if i could narrow that down
to one it would be great.

Joe

 

[Albert D. Kallal]

You don't quite say, for what, or when, or where these parsm are needed.

If you need to have users search on a form, then you can use what is called
query by form. This feature essentially turns a form into a query form, and
then any values (or conditions) can simply be entered into a form. (so for a
sales amount field you can type in > 500 and < 1000).

While looking at a form, to jump to query by form, you can go:

records->Filter->Filter by form.

The above works well for searching.

If you want something even more user friendly, then you can roll you own,
and creating something like:

http://www.attcanada.net/~kallal.msn/Search/index.html

If you are prompting for values to be sent to a report, then I suggest you
consider dumping the use of parameters, and use the where clause of the open
report. it is a lot more flexible, and more user friendly.

The following screen shots will give you an idea of what I mean:
http://www.attcanada.net/~kallal.msn/ridesrpt/ridesrpt.html

So, if you have a un-bound form that prompts for a city, and then has a
button to launch the report, you can go:

dim strWhere as string

if isnull(txtCity) = false then
strWhere = "City = '" & txtCity & "'"
end if

docmd.openReport "the report",acViewPreview,,strWhere

The above code is what I used for those report samples.

 

[terri]

You can put multiple criteria into a single query. I
have ones that have criteria in as many as 17 columns.
You may use the brackets for any or all of them. If the
person running the query simply hits the enter key without
putting anything in the prompt window, the query will take
that as no criteria defined for the field and will give
them the next prompt window.