Morning all:
I need some help with a query. This one has me rather stuck.
Background:
I have a chart embedded in the footer of a report. Obviously, the chart needs a query to run on. I have a basic query that works fine as it is. The problem comes in when I try to add criteria to the query.
Basicly, one of the fields in the query is called "Dept_Code". I want to set the criteria for dept_code based on the value of a option button on the form that launches the report.
If the option list = 1, then the criteria should be "Carrier".
If the option list = 2, then the criteria should be "Intermediary"
If the option button = 3, then the criteria should be "Carrier" OR "Intermediary"
I've writen the criteria as a Nested IIF statement. It works fine when the option list is equal to 1 or 2. The problem is when it equals 3.
I've tried setting the crieria to Null, Is Not Null, "'Carrier' OR 'Intermediary'" etc. Nothing seems to work.
Here is an example of the formula:
IIf([Forms]![frm_select_report]![opt_report]=1,"Carrier",IIf([Forms]![frm_select_report]![opt_report]=2,"Intermediary","'Carrier' OR 'Intermediary'"))
How do I either make the last part of the equation include both Carriers and Intermediaries or set the criteria to return all results.
Because there are only two department codes, this could be set to * and return all records.
Any help or suggestions will be GREATLY appreciated. I'm really stuck.
HELP!
I need some help with a query. This one has me rather stuck.
Background:
I have a chart embedded in the footer of a report. Obviously, the chart needs a query to run on. I have a basic query that works fine as it is. The problem comes in when I try to add criteria to the query.
Basicly, one of the fields in the query is called "Dept_Code". I want to set the criteria for dept_code based on the value of a option button on the form that launches the report.
If the option list = 1, then the criteria should be "Carrier".
If the option list = 2, then the criteria should be "Intermediary"
If the option button = 3, then the criteria should be "Carrier" OR "Intermediary"
I've writen the criteria as a Nested IIF statement. It works fine when the option list is equal to 1 or 2. The problem is when it equals 3.
I've tried setting the crieria to Null, Is Not Null, "'Carrier' OR 'Intermediary'" etc. Nothing seems to work.
Here is an example of the formula:
IIf([Forms]![frm_select_report]![opt_report]=1,"Carrier",IIf([Forms]![frm_select_report]![opt_report]=2,"Intermediary","'Carrier' OR 'Intermediary'"))
How do I either make the last part of the equation include both Carriers and Intermediaries or set the criteria to return all results.
Because there are only two department codes, this could be set to * and return all records.
Any help or suggestions will be GREATLY appreciated. I'm really stuck.
HELP!
