473,721 Members | 2,182 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 2182
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.c om
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.c om 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
5966
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 a group of Sales people, the customers they deal with and the business they transact with them. I've got my head around all the tables & some of the basic Query structures OK and am beginning to delve into creating the forms I need to be able...
1
2913
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 2000. The access file is in access 2000 format. I have a form that will hold the relevent parameters for the query/report that reports the statistics for all job records that match a certain criteria. These are: - A Customer Name.
10
19215
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 string i need to extract string "federico" and string "FGB" and separate then into another 2 strings:
2
2037
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 i specify the field i want to use as a parameter ?
4
1883
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 way to create a single query that will take parameters if they are present in the text boxes and not take parameters if the text box is empty? The ten fields will need to be searched using "Like" so that the user can search on part of a text...
5
2938
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 group. I don't wanna a form full of fields that ask the user for arguments. Instead a subform with a combobox where the user picks the , the , and the So I would have two tables groups and GroupCriteria for instance: group field ...
9
2445
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 criteria here - so ignore Else strWhere = strWhere & ",'" & Me.Text30.Value & "'"
0
1399
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 since I am the first to use it so... here you go: I have a report I am trying to recreate, the old one is in a PowerBuilder app and the author is no longer with company. The majority of the report is straightforward, but I am really hung up on...
1
5386
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 in the design view I put dates between two dates in the criteria. I want to create a form that will be the front end where a week beginning date and week ending date can be entered - and this will then be able to run the query with these dates in...
0
8738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9234
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9086
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8028
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6678
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4501
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4762
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3207
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2146
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.