Creating WHERE string criteria on the fly.

I want to create a form that will allow the user to select from a list of
available reports and based on a filter limit the records displayed in the
report. I have figured out how to access the reports collection to populate
a list box containing the report names. The filter I currently have lists
some key fields that I think the user may want to filter on. A WHERE string
is constructed based on the feilds that the user inputs values into. The
problem is the WHERE statement can only be constructed from feilds that I
think the user would want to filter on. I want to make it more flexible and
allow the user to select the fields they want to filter on and then input
values for the feilds selected to restrict the records displayed in the
report. Assuming the SELECT and FROM statement is static and includes all of
the relevant tables in the DB with all of the appropriate joins, the user
would be able to select any feild along with a limited value for that field
and filter the report that way. In addition, when making fields available
for constructing the WHERE statement it would be necessary to restrict the
fields that are available to only those that are included in the SELECT ...
FROM component of the query underlying the reports.

Anyone know how to do this?

re: Creating WHERE string criteria on the fly.

I'm guessing that most of your reports are based on queries. For those you
can request input form the user inthe criteria. eg, the user wnats
ifromation between two dates use BETWEEN [Enter first date] AND [Enter second
date]. When the report based on this query is selected the user is asked
"Enter first date" and after entering the first date is asked "Enter second
date." After the user enters the second date the report is printed with data
between the two dates.
You can create a passthrough query for any report based directly on a table.

rreitsma wrote:[color=blue]
>I want to create a form that will allow the user to select from a list of
>available reports and based on a filter limit the records displayed in the
>report. I have figured out how to access the reports collection to populate
>a list box containing the report names. The filter I currently have lists
>some key fields that I think the user may want to filter on. A WHERE string
>is constructed based on the feilds that the user inputs values into. The
>problem is the WHERE statement can only be constructed from feilds that I
>think the user would want to filter on. I want to make it more flexible and
>allow the user to select the fields they want to filter on and then input
>values for the feilds selected to restrict the records displayed in the
>report. Assuming the SELECT and FROM statement is static and includes all of
>the relevant tables in the DB with all of the appropriate joins, the user
>would be able to select any feild along with a limited value for that field
>and filter the report that way. In addition, when making fields available
>for constructing the WHERE statement it would be necessary to restrict the
>fields that are available to only those that are included in the SELECT ...
>FROM component of the query underlying the reports.
>
>Anyone know how to do this?[/color]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200606/1

re: Creating WHERE string criteria on the fly.

Hi:

Interesting problem. You might look at your system tables. In
msysobjects you want everyting with a type of 5, and then there's a
field called ID that gives a unique identifier to each query. Then if
you look at msysqueries you can find records with ObjectID matching your
ID; there's a record for each field in the query. With that you might
be able to populate your list box and go from there.

Good luck.

Jan

rreitsma wrote:[color=blue]
> I want to create a form that will allow the user to select from a
> list of available reports and based on a filter limit the records
> displayed in the report. I have figured out how to access the reports
> collection to populate a list box containing the report names. The
> filter I currently have lists some key fields that I think the user
> may want to filter on. A WHERE string is constructed based on the
> feilds that the user inputs values into. The problem is the WHERE
> statement can only be constructed from feilds that I think the user
> would want to filter on. I want to make it more flexible and allow
> the user to select the fields they want to filter on and then input
> values for the feilds selected to restrict the records displayed in
> the report. Assuming the SELECT and FROM statement is static and
> includes all of the relevant tables in the DB with all of the
> appropriate joins, the user would be able to select any feild along
> with a limited value for that field and filter the report that way.
> In addition, when making fields available for constructing the WHERE
> statement it would be necessary to restrict the fields that are
> available to only those that are included in the SELECT ... FROM
> component of the query underlying the reports.
>
> Anyone know how to do this?
>
>[/color]

re: Creating WHERE string criteria on the fly.

Stupid me. All that is available in the Field List type of combo or
list box. Talk about recreating the wheel! I never use that sort, so I
didn't think to look there. Oh well, nice try.

jahoobob via AccessMonster.com wrote:[color=blue]
> I'm guessing that most of your reports are based on queries. For
> those you can request input form the user inthe criteria. eg, the
> user wnats ifromation between two dates use BETWEEN [Enter first
> date] AND [Enter second date]. When the report based on this query
> is selected the user is asked "Enter first date" and after entering
> the first date is asked "Enter second date." After the user enters
> the second date the report is printed with data between the two
> dates. You can create a passthrough query for any report based
> directly on a table.
>
> rreitsma wrote:
>[color=green]
>> I want to create a form that will allow the user to select from a
>> list of available reports and based on a filter limit the records
>> displayed in the report. I have figured out how to access the
>> reports collection to populate a list box containing the report
>> names. The filter I currently have lists some key fields that I
>> think the user may want to filter on. A WHERE string is constructed
>> based on the feilds that the user inputs values into. The problem
>> is the WHERE statement can only be constructed from feilds that I
>> think the user would want to filter on. I want to make it more
>> flexible and allow the user to select the fields they want to
>> filter on and then input values for the feilds selected to restrict
>> the records displayed in the report. Assuming the SELECT and FROM
>> statement is static and includes all of the relevant tables in the
>> DB with all of the appropriate joins, the user would be able to
>> select any feild along with a limited value for that field and
>> filter the report that way. In addition, when making fields
>> available for constructing the WHERE statement it would be
>> necessary to restrict the fields that are available to only those
>> that are included in the SELECT ...[/color]
>[color=green]
>> FROM component of the query underlying the reports.[/color]
>[color=green]
>> Anyone know how to do this?[/color]
>
>[/color]