Report form Parameters

[Guest]

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth

 

[Guest]

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

You could do it in the same way with:

Like Forms!YourForm!YourComboBox & "*"

Note that the asterisk wildcard character need to be in quotes. However,
this requires the user to know that they have to leave the combo box Null to
return all rows, so is not very intuitive. A better approach would be to
include something like 'All' in the list. You can do this with a value list
as the combo box's RowSource if the values are fixed:

All;Open;Closed

or you can use an SQL statement which will list 'All' plus whatever values
are in the column in the table, so this is better if the value list is not a
list of fixed predetermined values, e.g. if the list were one of customers or
cities:

SELECT "All" AS Status, 0 AS OrderColumn
FROM YourTable
UNION
SELECT Status, 1
FROM YourTable
ORDER BY OrderColumn, Status;

As a UNION operation suppresses duplicates you'll get only one instance of
each value. The OrderColumn with the constants 0 or 1 as its values ensures
that the 'All' row comes first in the list whatever the other values are.

Whichever way you do it the criteria for the Status column in query design
view should be:

Forms!YourForm!YourComboBox Or Forms!YourForm!YourComboBox = "All"

If the user selects Open or Closed then the first part of this Boolean OR
operation will evaluate to TRUE for those rows containing the selected value,
so only those rows would be returned. If the user selects 'All' then the
second part of the OR operation will evaluate to TRUE for every row, so all
rows would be returned. If you save the query and then open it again in
design view you'll fund that Access has moved things around a bit, but it
will still work the same.

The DefaultValue property of the combo box should be set to 'All' so this
appears in the control when the form opens. The following code should be out
in the combo box's AfterUpdate event procedure so that, if a user sets the
value to Null by deleting the selected value it will automatically be set
back to 'All'

If IsNull(YourComboBox) Then
YourComboBox = "All"
End If

Ken Sheridan
Stafford, England

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

Two questions. In my combo box, I have the options open and closed, how
could I add an option for Both and also I when the box pulls up it brings
back every closed and open there is in the database. Can I narrow that down
to one a pice? Or should I make it values I manually entered into the box
instead of pulling from a field? And thanks for your input that really
helped!!!

You could do it in the same way with:

Like Forms!YourForm!YourComboBox & "*"

Note that the asterisk wildcard character need to be in quotes. However,
this requires the user to know that they have to leave the combo box Null to
return all rows, so is not very intuitive. A better approach would be to
include something like 'All' in the list. You can do this with a value list
as the combo box's RowSource if the values are fixed:

All;Open;Closed

or you can use an SQL statement which will list 'All' plus whatever values
are in the column in the table, so this is better if the value list is not a
list of fixed predetermined values, e.g. if the list were one of customers or
cities:

SELECT "All" AS Status, 0 AS OrderColumn
FROM YourTable
UNION
SELECT Status, 1
FROM YourTable
ORDER BY OrderColumn, Status;

As a UNION operation suppresses duplicates you'll get only one instance of
each value. The OrderColumn with the constants 0 or 1 as its values ensures
that the 'All' row comes first in the list whatever the other values are.

Whichever way you do it the criteria for the Status column in query design
view should be:

Forms!YourForm!YourComboBox Or Forms!YourForm!YourComboBox = "All"

If the user selects Open or Closed then the first part of this Boolean OR
operation will evaluate to TRUE for those rows containing the selected value,
so only those rows would be returned. If the user selects 'All' then the
second part of the OR operation will evaluate to TRUE for every row, so all
rows would be returned. If you save the query and then open it again in
design view you'll fund that Access has moved things around a bit, but it
will still work the same.

The DefaultValue property of the combo box should be set to 'All' so this
appears in the control when the form opens. The following code should be out
in the combo box's AfterUpdate event procedure so that, if a user sets the
value to Null by deleting the selected value it will automatically be set
back to 'All'

If IsNull(YourComboBox) Then
YourComboBox = "All"
End If

Ken Sheridan
Stafford, England

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum


:

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

Nevermind, I didnt read your whole response and thank you!

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

When I put the criteria in in the query is doesnt bring back my form, It
prompts for a parameter, just not my form. This is what i put........

[Forms]![Problem Status]![Problem]

Problem Status is my form name and Problem is the name of my combo box.

You could do it in the same way with:

Like Forms!YourForm!YourComboBox & "*"

Note that the asterisk wildcard character need to be in quotes. However,
this requires the user to know that they have to leave the combo box Null to
return all rows, so is not very intuitive. A better approach would be to
include something like 'All' in the list. You can do this with a value list
as the combo box's RowSource if the values are fixed:

All;Open;Closed

or you can use an SQL statement which will list 'All' plus whatever values
are in the column in the table, so this is better if the value list is not a
list of fixed predetermined values, e.g. if the list were one of customers or
cities:

SELECT "All" AS Status, 0 AS OrderColumn
FROM YourTable
UNION
SELECT Status, 1
FROM YourTable
ORDER BY OrderColumn, Status;

As a UNION operation suppresses duplicates you'll get only one instance of
each value. The OrderColumn with the constants 0 or 1 as its values ensures
that the 'All' row comes first in the list whatever the other values are.

Whichever way you do it the criteria for the Status column in query design
view should be:

Forms!YourForm!YourComboBox Or Forms!YourForm!YourComboBox = "All"

If the user selects Open or Closed then the first part of this Boolean OR
operation will evaluate to TRUE for those rows containing the selected value,
so only those rows would be returned. If the user selects 'All' then the
second part of the OR operation will evaluate to TRUE for every row, so all
rows would be returned. If you save the query and then open it again in
design view you'll fund that Access has moved things around a bit, but it
will still work the same.

The DefaultValue property of the combo box should be set to 'All' so this
appears in the control when the form opens. The following code should be out
in the combo box's AfterUpdate event procedure so that, if a user sets the
value to Null by deleting the selected value it will automatically be set
back to 'All'

If IsNull(YourComboBox) Then
YourComboBox = "All"
End If

Ken Sheridan
Stafford, England

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum


:

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Guest]

Just a quick FYI here. You can avoid having to hard code the criteria in
your query. If you don't put this criteria in the saved query, you can
actually use the same Report and base query and just set the filter at
runtime with:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourFieldnameOnReport]=" & Me!YourFieldOnForm

You can also do that with multiple criteria:

DoCmd.OpenReport "YourReportNameHere", acViewPreview, ,
"[YourFieldnameOnReport]=" & Me!YourFieldOnForm & " And
[YourSecondFieldNameOnReport]=" & Me!YourSecondFieldOnForm

So, in essence you can write one report but have code on the open report
button (or other way to open the report) to open a different report (even
though it is really the same) with each button.

--
Bob Larson
Access World Forums Super Moderator
____________________________________
Access 2000, 2003, 2007, SQL Server 2000, Crystal Reports 10/XI, VB6
WinXP, Vista

Nevermind, I didnt read your whole response and thank you!

I want to use a form, so they have a drop down list. How would I link it
back to the query and also be able to choose both Open and Closed at the same
time?

In your query you could set the parameter in your criteria field:

like [choose status] & *

This way when the user chooses Open he will only see the status open, when
he chooses closed the will only see closed. When he chooses nothing he'll see
all.

If you want to implement this in a form where the user first has to choose
from the form you could implement a unbound control which you can refer to in
your query.

hth
--
Maurice Ausum


:

Ok, I am a newby here. I have a report called MDB Data that is ran from a
query called MDB Data Query. What I want to happen is when the report is
clicked, I want it to prompt for a status parameter that gives the option of
Open or Closed. The field related to this is called Problem Status. Also,
is there a way to choose both Open and Closed at the same time instead of one
at a time?

The look of the parameter, I assume, can be created in a Form?

 

[Armen Stein]

Also see the free download at our site called Report Selection
Techniques:
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

Armen Stein
Microsoft Access MVP
www.JStreetTech.com