adding <> operators to IIF statement

Hello,

I have a form that includes a field for segmenting a table by customer "rating." The ratings are A, B, C, and Not Rated.

Here's how I'd like it to work:

If the user selects "C" as a rating, for example, the query should return all customers rated <=C. If the user selects "Not Rated," the query should only return customers who are "not rated."

To give you an example of my current approach, this is where I'm at (though it doesn't work!):

IIf([Forms]![generator]![rating]="not rated","not rated",([table1].[rating_code])<=[Forms]![generator]![rating])

I have put this code into the "criteria" row in query design view, in the column [table1].[rating_code].

I'm stumped...I've tried various approaches and can't seem to get it working.

Any help or hints would be greatly appreciated.

Thank you!

- John

re: adding <> operators to IIF statement

Okay, maybe I'm just thinking of this in the wrong terms? Maybe it's not a job for IIf?

I'm open to any suggestions, please :)

re: adding <> operators to IIF statement

You may be better off creating a form or subform based on the table. Then you can create a criteria string and open or filter the form with it when a button is clicked. This is usually preferable to having a query that depends on a form being open.

re: adding <> operators to IIF statement

thank you for the detailed reply, Chip! but I'm afraid that I'm not sure how to implement that.

would I do it in access sql view, and leave my form as it is? the form I've developed is based on the table.

thank you!

re: adding <> operators to IIF statement

by the way, if it's easier for you to explain it to me by way of telling me "go read about "criteria strings," or something to that effect, that would be just fine.

re: adding <> operators to IIF statement

If the form is based on the table, you can simply filter the form. How are you allowing the user to select the rating? Something like a combo box on the form header?

re: adding <> operators to IIF statement

the database consists of one table, a query, a macro to run the query, and a form. the way I made the form was to click "new" in the forms tab. I assume this means that form is based on the table, but I could be wrong?

yes. once I created the new form, I dragged a combo box into it and set it up that way.

re: adding <> operators to IIF statement

Ok, it sounds like what you want to do is filter the records shown on your form based on the combo box selection. You won't need a query to do this, since you can filter the form's recordset instead. You will need a way to trigger applying the filter, and the way to do that is either a command button like "Apply Filter" or the combo box's After_Update event. Just to make sure, does this sound like what you want?

re: adding <> operators to IIF statement

I think so...

I do have a command button on the form, which is currently used to run the macro which runs the query and displays the results. would I change the macro from OpenQuery to ApplyFilter? if so, would I then put criteria in the "Where Condition" field?

I should mention here that there are three other combo boxes in the form, all of which are referenced in the query. I've had no problems with those though, my problem is just getting the "ratings" logic worked out.

re: adding <> operators to IIF statement

For what it is worth, probably nothing, but when developing the logic for this, consider that someone is someday going to want just condition "B". This could be done with Multi-Select, Toggle Button set of <=, =, => Easier to choose method now, even if not implimented.

re: adding <> operators to IIF statement

thank you for the though, OldBirdman. that may come handy if I need a work around. I don't anticipate the need for wanting just one condition, except for the "not rated" condition, which is the one in question :)

re: adding <> operators to IIF statement

It sounds like your design requires that you display the query with results based on form values, while my objective was to avoid that and display the results on a form. That's simple if you want to filter on one field, but more complicated if you want to operate on several variables. Maybe someone used to using dynamic queries can offer some suggestions.

re: adding <> operators to IIF statement

yes, that's exactly right.

thanks for taking a shot at it Chip! your responses give me good things to consider; if not for this project, it may be handy on another.

re: adding <> operators to IIF statement

Can you post that query. I don't see why your IIF statement won't work.

Maybe you could also post what you tried.

re: adding <> operators to IIF statement

sure thing.

it works fine if the user selects "not rated" in the form. the query returns only records marked "not rated." when you select a rating in the form, the query comes back blank.

here's the query that I wish would work, and thanks for looking:

SELECT [scored_bio].id_number,[scored_bio].last_contact,[scored_bio].rating_code

FROM [scored_bio]

WHERE ((([scored_bio].last_contact)<=[Forms]![generator]![contact]) AND (([scored_bio].rating_code)=IIf([Forms]![generator]![rating]="not rated","not rated",([scored_bio].[rating_code])<=[Forms]![generator]![rating])) AND (([scored_bio].loyalty) Like [Forms]![generator]![loyalty]) AND (([scored_bio].percentile)<=[Forms]![generator]![percentile]))

ORDER BY [scored_bio with loyalty].credit;

re: adding <> operators to IIF statement

any thoughts on why this isn't working?

:)

re: adding <> operators to IIF statement

I've spent some time on this, and can't see why it shouldn't work. Of course, I can't be sure you have table rows that meet all these conditions.
Next step: Where this sql statement is used, replace the WHERE clause with or even and check your results. If correct, add the IIf(), then the other conditions, one at a time.

I just don't know what else to suggest. Good Luck!

re: adding <> operators to IIF statement

Thanks for the suggestion, OldBirdman.

The main issue though is how to select "not rated" people. If I use <= and select "not rated," then it returns everyone (not rated, C, B, and A) because the other ratings are "less" than "not rated," alphabetically.

Even if I recode "not rated" to something less than "A," I'll have the same problem in reverse. Hmm...

That's why I feel like the answer is in some sort of statement that might read (in English) "is less than or equal to [selection] unless selection is 'not rated' in which case return only 'not rated' individuals."

Puzzling!

re: adding <> operators to IIF statement

By the way, if I remove the IIf logic pertaining to "not rated" and just enter the following in design view it works:

<=[Forms]![generator]![rating]

But, as soon as I add IIf([Forms]![generator]![rating]="not rated","not rated" before it, it returns a blank sheet.

re: adding <> operators to IIF statement

Sorry to keep pounding this, but I just realized that if I recode all of the "not rated" records to just be blank, I can query for <=B and the nulls do not appear.

So now I'm trying to figure out the IIF statement that says something like:

IIf([Forms]![generator]![rating]="not rated",([scored_bio with loyalty].[rating_code]) Is Null,([scored_bio with loyalty].[rating_code])<=[Forms]![generator]![rating])

But for some reason that's not working either...I think I'm getting closer, but not quite there yet!

re: adding <> operators to IIF statement

Found something that works finally: Make several lines of criteria in your query under the rating field.

1. [Forms]![generator]![rating]="not rated" AND ="not rated"
2. [Forms]![generator]![rating]="C" AND (="C" OR ="B" OR ="A")
3. [Forms]![generator]![rating]="B" AND (="B" OR ="A")
4. [Forms]![generator]![rating]="A" AND ="A"

re: adding <> operators to IIF statement

Simplify that to:
1. =[Forms]![generator]![rating]
2. [Forms]![generator]![rating]="C" AND (="B" OR ="A")
3. [Forms]![generator]![rating]="B" AND ="A"

Or on one line:

=[Forms]![generator]![rating] OR ([Forms]![generator]![rating]="C" AND (="B" OR ="A")) OR ([Forms]![generator]![rating]="B" AND ="A")

re: adding <> operators to IIF statement

Chip, you da man.

Thank you so much! I expanded on your concept to include several more ratings and it scales up just fine.

Again, thanks everyone for helping me through this!