Help defining parameter form

[cuyeda]

I have a query that generates a list of species. Each species has an
"ActionType" associated with it. There can be 4 dif action types:

List(T)
List(E)
List(T/E)
n/a

I want to generate a parameter form to allow the user to filter the results.
In the parameter form I want a combo box which gives the user two options:
"List" or "CH".

If the user selects "CH" then the query should only display those records
where "n/a" appears (ie. the way I can filter the results by typing "n/a" in
the criteria field in design view). If the user selects "List" then I want
all the records where "n/a" does not appear (ie. the way I can filter results
by typing "Not Like 'n/a' " in design view).

I tried using the following but it didn't work:

iff([forms]![parameter form name]![name of control] = "CH", "n/a", Not Like
"n/a")

Thanks in advance for all your help.

 

[KARL DEWEY]

I am not sure what you are doing but try this --
iff([forms]![parameter form name]![name of control] = "CH", "n/a", <>"n/a")

 

[cuyeda]

Thanks Karl but it doesn't seem to work.

Sorry for previous poor explanation - let me try again.

I have a table with 3 fields:

ID (PrimaryKey) - AUTONUMBER
Name - TEXT
ActionType - TEXT (LookupWizard with 4 possible selections; (1) listT, (2)
listE, (3) listT/E (4) n/a)

I'm generating a query that displays all the species in the table with their
"ActionType". However, I only want to display species with certain action
types.

So I built a simple parameter form to let the user specify the search
criteria. Even though there are 4 dif ActionTypes I only want the user to
specify based on the following two criteria: List or CH. So the parameter
form has a combo box that gives these two options.

If they select "List", the query should show all the records where the
ActionType is either "listT", "listE", or "listT/E". If the user selects
"CH" then the query should only show records with ActionType "n/a".

I'm trying to determine how to write the code for this and seem to be
hitting a wall.
For example, in the design view of my query if I type into the Criteria row
of the ActionType field ' n/a ' the query will only show those records. If I
type: ' <> n/a ' it will show all the non n/a records. But if I try and make
the previously mentioned if statement it doesn't work.

Hmmm?

I am not sure what you are doing but try this --
iff([forms]![parameter form name]![name of control] = "CH", "n/a", <>"n/a")

--
KARL DEWEY
Build a little - Test a little

I have a query that generates a list of species. Each species has an
"ActionType" associated with it. There can be 4 dif action types:

List(T)
List(E)
List(T/E)
n/a

I want to generate a parameter form to allow the user to filter the results.
In the parameter form I want a combo box which gives the user two options:
"List" or "CH".

If the user selects "CH" then the query should only display those records
where "n/a" appears (ie. the way I can filter the results by typing "n/a" in
the criteria field in design view). If the user selects "List" then I want
all the records where "n/a" does not appear (ie. the way I can filter results
by typing "Not Like 'n/a' " in design view).

I tried using the following but it didn't work:

iff([forms]![parameter form name]![name of control] = "CH", "n/a", Not Like
"n/a")

Thanks in advance for all your help.

 

[KARL DEWEY]

Post the SQL of your query and maybe I'll get a better understanding.
--
KARL DEWEY
Build a little - Test a little

Thanks Karl but it doesn't seem to work.

Sorry for previous poor explanation - let me try again.

I have a table with 3 fields:

ID (PrimaryKey) - AUTONUMBER
Name - TEXT
ActionType - TEXT (LookupWizard with 4 possible selections; (1) listT, (2)
listE, (3) listT/E (4) n/a)

I'm generating a query that displays all the species in the table with their
"ActionType". However, I only want to display species with certain action
types.

So I built a simple parameter form to let the user specify the search
criteria. Even though there are 4 dif ActionTypes I only want the user to
specify based on the following two criteria: List or CH. So the parameter
form has a combo box that gives these two options.

If they select "List", the query should show all the records where the
ActionType is either "listT", "listE", or "listT/E". If the user selects
"CH" then the query should only show records with ActionType "n/a".

I'm trying to determine how to write the code for this and seem to be
hitting a wall.
For example, in the design view of my query if I type into the Criteria row
of the ActionType field ' n/a ' the query will only show those records. If I
type: ' <> n/a ' it will show all the non n/a records. But if I try and make
the previously mentioned if statement it doesn't work.

Hmmm?

I am not sure what you are doing but try this --
iff([forms]![parameter form name]![name of control] = "CH", "n/a", <>"n/a")

--
KARL DEWEY
Build a little - Test a little

I have a query that generates a list of species. Each species has an
"ActionType" associated with it. There can be 4 dif action types:

List(T)
List(E)
List(T/E)
n/a

I want to generate a parameter form to allow the user to filter the results.
In the parameter form I want a combo box which gives the user two options:
"List" or "CH".

If the user selects "CH" then the query should only display those records
where "n/a" appears (ie. the way I can filter the results by typing "n/a" in
the criteria field in design view). If the user selects "List" then I want
all the records where "n/a" does not appear (ie. the way I can filter results
by typing "Not Like 'n/a' " in design view).

I tried using the following but it didn't work:

iff([forms]![parameter form name]![name of control] = "CH", "n/a", Not Like
"n/a")

Thanks in advance for all your help.

 

[John Spencer]

You might try the following criteria

LIKE IIF([forms]![parameter form name]![name of control] = "CH",
"n/a","List*")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[cuyeda]

Thanks John! But a question: Why do you need to preceed the iif statement
with "Like" is it because on the arguments is a wildcard? Also, even though
your solution works I'd still like to find a way to write a code for "not
n/a" rather than the wildcard option which I'm assuming is finding all
records with the word 'list' in them. This is because later on I might add
new categories that do not have a 'list' in their title but are not 'n/a'.

Karl,
This is what I type into the design view of the parameter form:

IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",<>"n/a")
Or Like [forms]![PetitionReportParameters]![RequestedAction] Is Null

This is what the SQL then shows:

(([Petition(species)].[Listing
Action])=IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",([Petition(species)].[Listing
Action])<>"n/a"))) OR ((([Petition(species)].[Listing Action]) Like
[forms]![PetitionReportParameters]![RequestedAction] Is Null));

You might try the following criteria

LIKE IIF([forms]![parameter form name]![name of control] = "CH",
"n/a","List*")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a query that generates a list of species. Each species has an
"ActionType" associated with it. There can be 4 dif action types:

List(T)
List(E)
List(T/E)
n/a

I want to generate a parameter form to allow the user to filter the
results.
In the parameter form I want a combo box which gives the user two options:
"List" or "CH".

If the user selects "CH" then the query should only display those records
where "n/a" appears (ie. the way I can filter the results by typing "n/a"
in
the criteria field in design view). If the user selects "List" then I
want
all the records where "n/a" does not appear (ie. the way I can filter
results
by typing "Not Like 'n/a' " in design view).

I tried using the following but it didn't work:

iff([forms]![parameter form name]![name of control] = "CH", "n/a", Not
Like
"n/a")

Thanks in advance for all your help.

 

[John Spencer]

The following will return ALL records if the requested action is anything
other than CH

IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",<>"n/a")
Or [forms]![PetitionReportParameters]![RequestedAction] <> "CH"

To exclued the "N/A"
IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",<>"n/a")
Or ([forms]![PetitionReportParameters]![RequestedAction] <> "CH" AND
<> "N/A")

In the where clause that would look like

WHERE [Listing Action] =
IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",<>"n/a")
OR ([Listing Action] <> "N/A" and
[forms]![PetitionReportParameters]![RequestedAction]<>"CH")
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks John! But a question: Why do you need to preceed the iif statement
with "Like" is it because on the arguments is a wildcard? Also, even
though
your solution works I'd still like to find a way to write a code for "not
n/a" rather than the wildcard option which I'm assuming is finding all
records with the word 'list' in them. This is because later on I might
add
new categories that do not have a 'list' in their title but are not 'n/a'.

Karl,
This is what I type into the design view of the parameter form:

IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",<>"n/a")
Or Like [forms]![PetitionReportParameters]![RequestedAction] Is Null

This is what the SQL then shows:

(([Petition(species)].[Listing
Action])=IIf([forms]![PetitionReportParameters]![RequestedAction]="CH","n/a",([Petition(species)].[Listing
Action])<>"n/a"))) OR ((([Petition(species)].[Listing Action]) Like
[forms]![PetitionReportParameters]![RequestedAction] Is Null));

You might try the following criteria

LIKE IIF([forms]![parameter form name]![name of control] = "CH",
"n/a","List*")


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I have a query that generates a list of species. Each species has an
"ActionType" associated with it. There can be 4 dif action types:

List(T)
List(E)
List(T/E)
n/a

I want to generate a parameter form to allow the user to filter the
results.
In the parameter form I want a combo box which gives the user two
options:
"List" or "CH".

If the user selects "CH" then the query should only display those
records
where "n/a" appears (ie. the way I can filter the results by typing
"n/a"
in
the criteria field in design view). If the user selects "List" then I
want
all the records where "n/a" does not appear (ie. the way I can filter
results
by typing "Not Like 'n/a' " in design view).

I tried using the following but it didn't work:

iff([forms]![parameter form name]![name of control] = "CH", "n/a", Not
Like
"n/a")

Thanks in advance for all your help.