Use Criteria for Parameter Prompt

  • Thread starter Thread starter jhicsupt
  • Start date Start date
J

jhicsupt

I would like to prompt the user to return 1 of 3 different sets of records:

The values in the field [Segment] are "E" or "B" or "C"

I would to either return
= "E" (only records that are coded with an "E")
<> "E" (only records that are not coded with an "E")
All records (all records, regardless of what they are coded)

So I was thinking I would have the prompt:
[Enter 1 for "E", 2 for <> "E", 3 for All Records)

Thanks in advance.
 
It can be done.

The Where clause would need to look like the following

(Segment = "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 1)
OR
(Segment <> "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 2)
OR
[Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 3

I have no idea what query design view would do with that. Probably just add a
calculated field [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] and set
the criteria to 1, 2, or 3 on three criteria lines and = E and <> "E" on the
appropriate criteria lines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
I pasted your code (Segment = "E"...) in Design View and it is still
returning all of the records.

Sorry, but I do not know what you mean by adding a calculated field. Can
you explain further?

John Spencer MVP said:
It can be done.

The Where clause would need to look like the following

(Segment = "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 1)
OR
(Segment <> "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 2)
OR
[Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 3

I have no idea what query design view would do with that. Probably just add a
calculated field [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] and set
the criteria to 1, 2, or 3 on three criteria lines and = E and <> "E" on the
appropriate criteria lines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I would like to prompt the user to return 1 of 3 different sets of records:

The values in the field [Segment] are "E" or "B" or "C"

I would to either return
= "E" (only records that are coded with an "E")
<> "E" (only records that are not coded with an "E")
All records (all records, regardless of what they are coded)

So I was thinking I would have the prompt:
[Enter 1 for "E", 2 for <> "E", 3 for All Records)

Thanks in advance.
Click to expand...
Click to expand...
 
In a field block enter
[Enter 1 for 'E', 2 for <> 'E', 3 for All Records]
and then set criteria for that as followsL

Field: [Enter 1 for 'E', 2 for <> 'E', 3 for All Records]
Criteria(1): 1
Criteria(2): 2
Criteria(3): 3

Under your field named Segment, enter
Field: Segment
Criteria(1): "E"
Criteria(2): <> "E"
Criteria(3): <<< Leave this blank >>>

That should do what you want. If not, copy the SQL statement and post
it here (Hint: Menu View: SQL).

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

I pasted your code (Segment = "E"...) in Design View and it is still
returning all of the records.

Sorry, but I do not know what you mean by adding a calculated field. Can
you explain further?

John Spencer MVP said:
It can be done.

The Where clause would need to look like the following

(Segment = "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 1)
OR
(Segment <> "E" AND [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 2)
OR
[Enter 1 for 'E', 2 for <> 'E', 3 for All Records] = 3

I have no idea what query design view would do with that. Probably just add a
calculated field [Enter 1 for 'E', 2 for <> 'E', 3 for All Records] and set
the criteria to 1, 2, or 3 on three criteria lines and = E and <> "E" on the
appropriate criteria lines.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I would like to prompt the user to return 1 of 3 different sets of records:

The values in the field [Segment] are "E" or "B" or "C"

I would to either return
= "E" (only records that are coded with an "E")
<> "E" (only records that are not coded with an "E")
All records (all records, regardless of what they are coded)

So I was thinking I would have the prompt:
[Enter 1 for "E", 2 for <> "E", 3 for All Records)

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top