Creating WHERE string criteria on the fly.

[rreitsma]

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?

[jahoobob via AccessMonster.com]

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:

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?

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

[Jan]

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:

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?

[Jan]

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:

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:

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?