A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Criteria within a query



 
 
Thread Tools Display Modes
  #1  
Old July 10th, 2008, 04:54 AM posted to microsoft.public.access.queries
Kerry
external usenet poster
 
Posts: 132
Default 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  
Old July 10th, 2008, 05:43 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old July 10th, 2008, 05:55 AM posted to microsoft.public.access.queries
Kerry
external usenet poster
 
Posts: 132
Default 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  
Old July 10th, 2008, 06:10 AM posted to microsoft.public.access.queries
Kerry
external usenet poster
 
Posts: 132
Default 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  
Old July 10th, 2008, 07:35 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:52 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.