If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional Criteria within a query
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!! |
#2
|
|||
|
|||
Conditional Criteria within a query
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kerry" wrote in message ... 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!! |
#3
|
|||
|
|||
Conditional Criteria within a query
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" wrote: 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!! |
#4
|
|||
|
|||
Conditional Criteria within a query
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! "Allen Browne" wrote: 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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kerry" wrote in message ... 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!! |
#5
|
|||
|
|||
Conditional Criteria within a query
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Kerry" wrote in message ... 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! "Allen Browne" wrote: 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" wrote in message ... 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. |
Thread Tools | |
Display Modes | |
|
|