473,218 Members | 1,751 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,218 software developers and data experts.

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?
Jun 20 '06 #1
3 2143
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
Jun 20 '06 #2
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?

Jun 23 '06 #3
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?


Jun 23 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Iain Miller | last post by:
Now this shouldn't be hard but I've been struggling on the best way as to how to do this one for a day or 3 so I thought I'd ask the assembled company..... I'm writing an application that tracks...
1
by: longtim | last post by:
I have been having endless difficulty creating reports/queries that set any relevent parameters from controls in forms. I am creating an application under access 2003 but will target access...
10
by: Federico G. Babelis | last post by:
Hi, I need to extract a string from another string separated by "," like a .csv file. for example I have this string: String1 = 000,federico,00,439827HGH,1233,FGB,0000,00,000 and from that...
2
by: Sagaert Johan | last post by:
Hi I try to use this as sql string ( The field i compare with is variable. ) "SELECT * FROM FMatrix WHERE @wantedfield=@criteria" i use the Parameters.Add with OleDbType.WChar . Why can't...
4
by: DeanL | last post by:
Hi Guys, I need some help creating a query that is going to take between 1 and 10 parameters. The parameters are entered on a form into text boxes that may have data or be empty. Is there a...
5
by: Kaspa | last post by:
I am creating a contact database and I would like the user to create his own groups of contacts based on criterias, thefore I would like to let him pick the fields and criteria for a particular...
9
by: Drum2001 | last post by:
Hello All! I am using the following code during an On Click event for a button. It works properly to create a query for a report: Private Sub Command14_Click() If IsNull(Me.Text30) Then ' No...
0
by: mmueller | last post by:
I am new to reporting services 2005 (reporting in Access for years and older versions of Reporting Services from time to time) and this is probably a dumb question... but I have no internal resources...
1
by: as323 | last post by:
Hi There, I am quite new to Access 2003 and am looking for someone to help me with a project im starting on. I currently have a query that is run which will pull up records on selected data and...
0
by: veera ravala | last post by:
ServiceNow is a powerful cloud-based platform that offers a wide range of services to help organizations manage their workflows, operations, and IT services more efficiently. At its core, ServiceNow...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.