advice on setting parameters

[jim]

Is anyone able to provide me with a link to useful documentation or
just outright explain to me how to set query parameters dynamically?

I'm really new to Access and databases in general but I suspect that
there is a way to insert criteria into a query besides opening it up
and typing "and" and "or" on the criteria row of each column.

I have a query that shows thousands of records (students in different
classes taking different tests) and I'd like to be able to produce
reports that show just the records for specific tests, and reports that
show records for specific teachers, and perhaps reports for specific
grade levels, and perhaps look at the data in other unforeseen ways.
How can I dynamically select criteria to sort this query?

Do I have to make a new query or change the query manually each time I
want to run a different sort of report?

Thanks for the advice.

Jim.

[Allen Browne]

The simplest solution would be to leave the criteria out of the query, and
build the WhereCondition for OpenReport so it filters the way you want.

There's an example of how to build the criteria string dynamically from only
those boxes where the user actually typed something, in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The downloadable example actually sets the Filter for the form, but it is
exactly the same process to set the WhereCondition for OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim" <0.***********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Is anyone able to provide me with a link to useful documentation or
just outright explain to me how to set query parameters dynamically?

I'm really new to Access and databases in general but I suspect that
there is a way to insert criteria into a query besides opening it up
and typing "and" and "or" on the criteria row of each column.

I have a query that shows thousands of records (students in different
classes taking different tests) and I'd like to be able to produce
reports that show just the records for specific tests, and reports that
show records for specific teachers, and perhaps reports for specific
grade levels, and perhaps look at the data in other unforeseen ways.
How can I dynamically select criteria to sort this query?

Do I have to make a new query or change the query manually each time I
want to run a different sort of report?

Thanks for the advice.

Jim.

[jim]

That looks like the right idea but in your documentation you mention
that using the form on a query is a bad idea.

In my case the data I want to filter has been assembled by a query from
multiple tables.
Also I am ultimately interested in printing reports of filtered data
and I'm not sure how to do that if the filtered results are being
displayed within the form.

Jim.

Allen Browne wrote:

The simplest solution would be to leave the criteria out of the query, and
build the WhereCondition for OpenReport so it filters the way you want.

There's an example of how to build the criteria string dynamically from only
those boxes where the user actually typed something, in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The downloadable example actually sets the Filter for the form, but it is
exactly the same process to set the WhereCondition for OpenReport.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim" <0.***********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegro ups.com...

Is anyone able to provide me with a link to useful documentation or
just outright explain to me how to set query parameters dynamically?

I'm really new to Access and databases in general but I suspect that
there is a way to insert criteria into a query besides opening it up
and typing "and" and "or" on the criteria row of each column.

I have a query that shows thousands of records (students in different
classes taking different tests) and I'd like to be able to produce
reports that show just the records for specific tests, and reports that
show records for specific teachers, and perhaps reports for specific
grade levels, and perhaps look at the data in other unforeseen ways.
How can I dynamically select criteria to sort this query?

Do I have to make a new query or change the query manually each time I
want to run a different sort of report?

Thanks for the advice.

Jim.

[Allen Browne]

You can use the technique with a query.

The article suggests that using dynamic criteria like that is better than
using *parameters* in a query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"jim" <0.***********@gmail.comwrote in message
news:11**********************@k70g2000cwa.googlegr oups.com...

That looks like the right idea but in your documentation you mention
that using the form on a query is a bad idea.

In my case the data I want to filter has been assembled by a query from
multiple tables.
Also I am ultimately interested in printing reports of filtered data
and I'm not sure how to do that if the filtered results are being
displayed within the form.

Jim.

Allen Browne wrote:

>The simplest solution would be to leave the criteria out of the query,
and
build the WhereCondition for OpenReport so it filters the way you want.

There's an example of how to build the criteria string dynamically from
only
those boxes where the user actually typed something, in this article:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
The downloadable example actually sets the Filter for the form, but it is
exactly the same process to set the WhereCondition for OpenReport.

"jim" <0.***********@gmail.comwrote in message
news:11**********************@m7g2000cwm.googlegr oups.com...

Is anyone able to provide me with a link to useful documentation or
just outright explain to me how to set query parameters dynamically?

I'm really new to Access and databases in general but I suspect that
there is a way to insert criteria into a query besides opening it up
and typing "and" and "or" on the criteria row of each column.

I have a query that shows thousands of records (students in different
classes taking different tests) and I'd like to be able to produce
reports that show just the records for specific tests, and reports that
show records for specific teachers, and perhaps reports for specific
grade levels, and perhaps look at the data in other unforeseen ways.
How can I dynamically select criteria to sort this query?

Do I have to make a new query or change the query manually each time I
want to run a different sort of report?

[Albert D. Kallal]

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:
dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Arno R]

Suggestion for much cleaner code:

When there are more combo's involved I often start with:
Dim strWhere as string
strWhere="1=1"
'This is to get rid of the *repeating* syntax if strWhere <"" then ....

'So I add the criteria for the not-empty-combo's simply with
If Not IsNull(Me!cbotxtField) then strWhere=strWhere & " AND txtField = '" & Me!cbotxtField & "'"
If Not IsNull(Me!cboNumField) then strWhere=strWhere & " AND NumField = " & Me!cboNumField
---
---
Docmd.OpenReport "ReportName" , , , strWhere

Arno R
"Albert D. Kallal" <Pl*******************@msn.comschreef in bericht news:FEQXg.134557$5R2.8926@pd7urf3no...

As you are finding out, putting forms! expression in queries can get really
messy real fast.

Even worse, is now that the query is now "married" and attached to that ONE
form. Often, I have a nice query that I could use MANY times for different
reports, and often even that same query could be used for reports...but then
someone comes along and puts in a expression that means the query is ONLY
good when that form is opened.

Worse, is very hard to control things like having 5 combo boxes, but the
user only selects restrictions in 3 of the combo boxes...and wants the other
2 to be ignore.

I could probably write another 10 or pages as to why putting forms
expressions in queries is bad (besides...it makes the queries real ugly, and
hard to read. and, the sql then is not standard anymore (it will not work
with server based systems either).

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query, BUT NO FORMS! conditions are
placed in the query.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

Take a look at the following screen shots to see what I mean:

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:
dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com

[Bas Cost Budde]

Instead of "1=1" you could write "True", right? And yes, it is my
observation too that both

expression OR false

and

expression AND true

are equivalent to the expression. Which means that when composing a
conjunct (several AND clauses) or a disjunct (several OR clauses), this
simple starting value helps a lot.

Arno R schreef:

Suggestion for much cleaner code:

When there are more combo's involved I often start with:
Dim strWhere as string
strWhere="1=1"
'This is to get rid of the *repeating* syntax if strWhere <"" then ....

--
Bas Cost Budde
Holland
www.heuveltop.nl/BasCB/msac_index.html