option parameter

[inungh]

I would like to let users to choose a radio button and get different
result from the query.

I would like to know how to implment it.
For example, the radio button has 3 choices like "ALL", "CURRENT", and
"PAST"

In the table I have a field called MyStatus.

I would like to get MyStatus value "0" and "1" for ALL
and CURRENT for "1" and PAST for value of "0"


There is no null in the field. Only 0 or 1 in the field.


Your help is great appreciated,

 

[bhicks11 via AccessMonster.com]

Probably the easiest way would be to create three separate queries with the
appropriate criteria and run the query from the button. So add the criteria
"0" or "1" to the MyStatus field in the query to run from the ALL button, etc.


There are lots of other ways to do, passing the parameter or in VBA code, but
this but this would be easiest.

Bonnie
http://www.dataplus-svc.com

 

[John Spencer]

The option group (radio buttons) will return values of 1,2, or 3 (unless you
have specified other values for the radio buttons).

Field: MyStatus
Criteria: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[inungh]

Probably the easiest way would be to create three separate queries with the
appropriate criteria and run the query from the button.  So add the criteria
"0" or "1" to the MyStatus field in the query to run from the ALL button,etc.

There are lots of other ways to do, passing the parameter or in VBA code,but
this but this would be easiest.

Bonniehttp://www.dataplus-svc.com

Thanks for the information,
The query is used for report.
If I have 3 queries then I need 3 reprots to maintain.
I would like to have one report and one query to maintain.

I can genearte SQL on Report Open and change the criteria of the SQL
which is OK.

I hope to use "IIF" statement in the query that the query to take care
everything.

Thanks again for the information,

 

[inungh]

The option group (radio buttons) will return values of 1,2, or 3 (unless you
have specified other values for the radio buttons).

Field: MyStatus
Criteria: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I would like to let users to choose a radio button and get different
result from the query.

I would like to know how to implment it.
For example, the radio button has 3 choices like "ALL", "CURRENT", and
"PAST"

In the table I have a field called MyStatus.

I would like to get MyStatus value "0" and "1" for ALL
and CURRENT for "1" and PAST for value of "0"

There is no null in the field. Only 0 or 1 in the field.

Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks for the message,
the value o and 1 works, but in (0,1) for some reason it does not
work,
Can you please let me know what am I missing?

Thanks again,

 

[John Spencer]

If you are using the query design view try it this way:

Field: MySearch: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

Criteria: <> False

If that fails, check and see what value is being returned by putting the
following in the query
Field: TheValue: [Forms].[YourFormName].[NameOfOptionGroup]

I did a simple test and it works for me. Unless your separator character is
not the comma but the semi-colon. Then you might need to use In (0;1) instead
of In (0,1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

The option group (radio buttons) will return values of 1,2, or 3 (unless you
have specified other values for the radio buttons).

Field: MyStatus
Criteria: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I would like to let users to choose a radio button and get different
result from the query.
I would like to know how to implment it.
For example, the radio button has 3 choices like "ALL", "CURRENT", and
"PAST"
In the table I have a field called MyStatus.
I would like to get MyStatus value "0" and "1" for ALL
and CURRENT for "1" and PAST for value of "0"
There is no null in the field. Only 0 or 1 in the field.
Your help is great appreciated,- Hide quoted text -

- Show quoted text -

Thanks for the message,
the value o and 1 works, but in (0,1) for some reason it does not
work,
Can you please let me know what am I missing?

Thanks again,

 

[inungh]

If you are using the query design view try it this way:

Field: MySearch: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

Criteria: <> False

If that fails, check and see what value is being returned by putting the
following in the query
Field: TheValue: [Forms].[YourFormName].[NameOfOptionGroup]

I did a simple test and it works for me.  Unless your separator character is
not the comma but the semi-colon.  Then you might need to use In (0;1) instead
of In (0,1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

The option group (radio buttons) will return values of 1,2, or 3 (unless you
have specified other values for the radio buttons).
Field: MyStatus
Criteria: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
inungh wrote:
I would like to let users to choose a radio button and get different
result from the query.
I would like to know how to implment it.
For example, the radio button has 3 choices like "ALL", "CURRENT", and
"PAST"
In the table I have a field called MyStatus.
I would like to get MyStatus value "0" and "1" for ALL
and CURRENT for "1" and PAST for value of "0"
There is no null in the field. Only 0 or 1 in the field.
Your help is great appreciated,- Hide quoted text -
- Show quoted text -

Thanks for the message,
the value o and 1 works, but in (0,1) for some reason it does not
work,
Can you please let me know what am I missing?

Thanks again,- Hide quoted text -

- Show quoted text -

Thanks millions, it works now,

 

[inungh]

If you are using the query design view try it this way:

Field: MySearch: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)

Criteria: <> False

If that fails, check and see what value is being returned by putting the
following in the query
Field: TheValue: [Forms].[YourFormName].[NameOfOptionGroup]

I did a simple test and it works for me.  Unless your separator character is
not the comma but the semi-colon.  Then you might need to use In (0;1) instead
of In (0,1)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

inunghwrote:
The option group (radio buttons) will return values of 1,2, or 3 (unless you
have specified other values for the radio buttons).
Field: MyStatus
Criteria: Choose([Forms].[YourFormName].[NameOfOptionGroup]
,[MyStatus] in (0,1),[MyStatus]=1,[MyStatus]=0)
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
inunghwrote:
I would like to let users to choose a radio button and get different
result from the query.
I would like to know how to implment it.
For example, the radio button has 3 choices like "ALL", "CURRENT", and
"PAST"
In the table I have a field called MyStatus.
I would like to get MyStatus value "0" and "1" for ALL
and CURRENT for "1" and PAST for value of "0"
There is no null in the field. Only 0 or 1 in the field.
Your help is great appreciated,- Hide quoted text -
- Show quoted text -
Thanks for the message,
the value o and 1 works, but in (0,1) for some reason it does not
work,
Can you please let me know what am I missing?
Thanks again,- Hide quoted text -

- Show quoted text -

Thanks millions, it works now,- Hide quoted text -

- Show quoted text -

I just tried to change from Choose to SQL, but it does not work.
I just wonder can I replace choose as following:

Select MySQL from MyTable where MyID = [forms]![myForm]![myDropDown]

MySQL contains [MyStatus] = 1, [MyStatus] = 0, [MyStatus] in (0,1) for
My ID 1,2,3

For me, it seems to give the query same SQL string, but it does not
work if I use SQL to retrieve data from table instead of Choose
function.


Thanks again for helping,