Conditional Criteria within a query

[Kerry]

Hi! I've got the following fields and I want the second to contain criteria
contingent on the value of the first:

Dept_NBR is a number coming from a form selection of multiple #s and if it
is 28, I want Class_NBR to be 7 or 20 or 22. If Dept_NBR is not 28, I want
the Class_NBR criteria to be null.

Not sure where to start with this...any help is appreciated!!

 

[Allen Browne]

I don't understand how you want to assign this value in a query.

Your table has 2 fields: Dept_NBR, and Class_NBR.
If Dept_NBR is 28, ClassNBR should be restricted to 7, 20, or 22.
If Dept_NBR is not 28, ClassNBR should be Null.

There is no clear way to decide which of the 3 values to use when Dept_NBR
is 28. We also don't know when or why there could be invalid entries in
Class_NBR, and whether you want to destroy those values, suppress them,
prevent them in the first place, or ...

To prevent them, you could use a validation rule on your table:
1. Open the table in design view.

2. Open the Properties sheet.

3. Beside the Validation Rule in the Properties sheet, enter something like
this:
(([Dept_NBR = 28) AND ([Class_NBR] IN (7, 20, 22)) OR [Class_NBR] Is Null

Be sure you use the Validation Rule in the Properties sheet (the one for the
table), not the one in the lower pane of table design (which is the rule for
a field.)

 

[Kerry]

So, for some more details, here's the Where clause I've got and I need to
make([TBL_0100_SMS_COMPARE].[MER_CLASS_NBR])=7 or 20 or 22
when([TBL_0100_SMS_COMPARE].[MER_DEPT_NBR])=28 or null when not 28...can I
nest another IIf in here? When I try I'm getting a "wrong # of arguments
used in the expression" error...

WHERE (((TBL_0100_SMS_COMPARE.SIS_DT)>#4/1/2006#) AND
(([SIS_RETL]-[SMS_RETL])>0.04) AND
((IIf([Forms]![FRM_000_GET_MARKET]![Combo13]='ALL',True,([TBL_0100_SMS_COMPARE].[MER_DEPT_NBR])=[Forms]![FRM_000_GET_MARKET]![Combo13]))<>False)
AND (([SIS_RETL]*0.98)>=[SMS_RETL]));

 

[Kerry]

I see where you're headed...the table is created each time a report is run
thru a series of make/delete/append queries, so I think the validation would
be deleted.

Can I use

(([Dept_NBR = 28) AND ([Class_NBR] IN (7, 20, 22)) OR [Class_NBR] Is Null

in one of those queries to restrict the results?

My data looks something like this:

Dept_NBR, Class_NBR, Retail
21, 1, 1.39
21, 2, 1.49
22, 1, 1.39
24, 7, 1.59
28, 7, 159.00
28, 3, 459.00
....

and my form is used to select an individual Dept_NBR or ALL Dept_NBRs and
report out the Dept_NBR, Class_NBR, Retail.

In the case above, I'm looking to not return anything in Dept_NBR 28
Class_Nbr 7, Dept_NBR 28 Class_Nbr 20, or Dept_NBR 28 Class_Nbr 22, so the
report (if 28 was selected in the form) should return only 28, 3, 459.00.


THANKS!

I don't understand how you want to assign this value in a query.

Your table has 2 fields: Dept_NBR, and Class_NBR.
If Dept_NBR is 28, ClassNBR should be restricted to 7, 20, or 22.
If Dept_NBR is not 28, ClassNBR should be Null.

There is no clear way to decide which of the 3 values to use when Dept_NBR
is 28. We also don't know when or why there could be invalid entries in
Class_NBR, and whether you want to destroy those values, suppress them,
prevent them in the first place, or ...

To prevent them, you could use a validation rule on your table:
1. Open the table in design view.

2. Open the Properties sheet.

3. Beside the Validation Rule in the Properties sheet, enter something like
this:
(([Dept_NBR = 28) AND ([Class_NBR] IN (7, 20, 22)) OR [Class_NBR] Is Null

Be sure you use the Validation Rule in the Properties sheet (the one for the
table), not the one in the lower pane of table design (which is the rule for
a field.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I've got the following fields and I want the second to contain
criteria
contingent on the value of the first:

Dept_NBR is a number coming from a form selection of multiple #s and if it
is 28, I want Class_NBR to be 7 or 20 or 22. If Dept_NBR is not 28, I
want
the Class_NBR criteria to be null.

Not sure where to start with this...any help is appreciated!!

 

[Allen Browne]

Oh, so you only want to report the Class_NBR if it is one of the 3 values
and also Dept_NBR is 28? For any other values/combinations, you want to
suppress the Class_NBR?

Try an expression like this in the Field row in query design:
IIf(([Dept_NBR]=28) AND ([Class_NBR] IN (7, 20, 22))), [Class_NBR], Null)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I see where you're headed...the table is created each time a report is run
thru a series of make/delete/append queries, so I think the validation
would
be deleted.

Can I use

(([Dept_NBR = 28) AND ([Class_NBR] IN (7, 20, 22)) OR [Class_NBR] Is Null

in one of those queries to restrict the results?

My data looks something like this:

Dept_NBR, Class_NBR, Retail
21, 1, 1.39
21, 2, 1.49
22, 1, 1.39
24, 7, 1.59
28, 7, 159.00
28, 3, 459.00
...

and my form is used to select an individual Dept_NBR or ALL Dept_NBRs and
report out the Dept_NBR, Class_NBR, Retail.

In the case above, I'm looking to not return anything in Dept_NBR 28
Class_Nbr 7, Dept_NBR 28 Class_Nbr 20, or Dept_NBR 28 Class_Nbr 22, so the
report (if 28 was selected in the form) should return only 28, 3, 459.00.


THANKS!

I don't understand how you want to assign this value in a query.

Your table has 2 fields: Dept_NBR, and Class_NBR.
If Dept_NBR is 28, ClassNBR should be restricted to 7, 20, or 22.
If Dept_NBR is not 28, ClassNBR should be Null.

There is no clear way to decide which of the 3 values to use when
Dept_NBR
is 28. We also don't know when or why there could be invalid entries in
Class_NBR, and whether you want to destroy those values, suppress them,
prevent them in the first place, or ...

To prevent them, you could use a validation rule on your table:
1. Open the table in design view.

2. Open the Properties sheet.

3. Beside the Validation Rule in the Properties sheet, enter something
like
this:
(([Dept_NBR = 28) AND ([Class_NBR] IN (7, 20, 22)) OR [Class_NBR] Is Null

Be sure you use the Validation Rule in the Properties sheet (the one for
the
table), not the one in the lower pane of table design (which is the rule
for
a field.)

Hi! I've got the following fields and I want the second to contain
criteria
contingent on the value of the first:

Dept_NBR is a number coming from a form selection of multiple #s and if
it
is 28, I want Class_NBR to be 7 or 20 or 22. If Dept_NBR is not 28, I
want
the Class_NBR criteria to be null.