Hello thanks so much for all your help.
i've never done this type of thing before so its a bit foreign to me.
i made the combo box with list of fields
for the operators i could only do a list box - is that ok
and then i just placed an unbound box to be able to type in the criteria
and then did you say that i should have a button next to it that has the
following code:
DoCmd.OpenReport "rptYourName", acPreview, , strWhere
but what should i type after strWhere
also could i place the combo boxed and the unbound box 3 times on the form
that the user can base it on three criterias
thanks so much
i think i'm getting there!
:
The basis of the query by form is allowing users to select a field,
sign/operator, and criteria. This information is then used to build a
dynamic
query. In it's simplest form:
- create a combo box with a list of fields
- create a combo box with operators
- create a text box or combo box to enter/select a value
- write some code that builds a where condition that can be used in the
DoCmd.OpenReport "rptYourName", acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP
:
yes i guess you're right i wont give them an option to choose which
fields
just the criteria that they want.
so if i wanted like i explained before a combo box to choose the field
heading and then another one to choose the equation sign e.g =, <, >,
etc.
and then a box to fill in the criteria how do i do that? is it in the
applet
- i didn't find it
thanks so so much
really appreiciate all your help
:
My first reply to you has a link to an applet that allows users to
select
fields, operators, and criteria. You may need to modify the solution
to apply
the "where condition" to open an existing report.
I wouldn't go through the effort of allowing users the ability to
select the
fields they want to display in a published report. Each field would
have
varying sizes and types so it would be near impossible to get a
report to
look good without a ton of code. That's why I use the applet from my
first
reply to you. Users are happy getting the results sent to Excel or
other
programs where they are comfortable laying it out and printing.
--
Duane Hookom
Microsoft Access MVP
:
Yes thats exactly what i need - i would want both - that they are
able to
select records based on the criteria they enter and also which
fields.
The fields part is not as important, but even without that i need
to know
how to create a list or combo box where the user can choose the
field name to
enter the critera e.g field name would be Age then a box to choose
and then
criteria box would be 20 - that would select students over 20 years
old
Thanks a million for all your help
:
A report criteria form is an unbound form that allows users to
enter criteria
or filtering information for a report.
Are you asking how to allow the user to determine the records
that display
in a report or do you think you need to allow users to also
select the fields
that display in the report?
--
Duane Hookom
Microsoft Access MVP
:
yes that sounds like what i am trying to do.
but what is a report criteria form?
and when i create a combo or text box how can i get it that it
gives me the
field headings of the query - not the data of the query
thanks so much for all your help
:
Apparently an existing report was opened and filtered based
on entries in
controls on a form. I generally place a bunch of text, combo,
list, and check
boxes on a "report criteria" form. One list box has a Row
Source that is
based on a table of report names that I created.
I then use code in the On Click of a "Run Report" button that
builds a where
condition string:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " And [DateFld]>=#" & _
Me.txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " And [DateFld]<=#" & _
Me.txtEnd & "# "
End If
' more similar code
DoCmd.OpenReport lboReport, acPreview, , strWhere
--
Duane Hookom
Microsoft Access MVP
:
I didn't see how it was done exactly but basically by
choosing the criteria
in the form that i mentioned this criteria would go through
to a query and
then my clicking on a button a report would be created
based on that query.
The report would already be set with all the correct fields
the data would
just change according to the criteria chosen
thanks a lot
:
Define "creates a report"...
--
Duane Hookom
Microsoft Access MVP
:
hi
it looks very interesting but i was thinking of
something a bit different.
I once saw on a database a form with a drop down menu
to choose the field
you want then a drop down menu to choose the function
e.g =, <, > etc and
then a value box to put in the criteria.
and this then creates a report
how do i do that?
thanks so much for your time and help
:
Use the query by form applet found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
Users can create and modify their query/reports and
easily send them to
print, Excel, Word, HTML, Graph,...
--
Duane Hookom
Microsoft Access MVP
:
Hello
I am making a database for a college. It has to be
able to record all the
students who apply, who gets accepted / refused /
declines acceptances. Lists
of students in each class etc. etc.
I have customised many reports for the user but I
cannot prepare every
single eventuality and she wants to be able to
create for herself the most
useful reports that each time she needs something
she can create exactly what
she wants and not just use one of my preset ones
that will "do the job"
how can i create something that doesn't look
complicated but will enable the
user to have a lot of flexibility with the data?
thanks a lot
