adding <> operators to IIF statement

[seagullino]

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

[seagullino]

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 :)

[ChipR]

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.

Private Sub cmdShowRecords_Click(Cancel As Integer)
  Dim strCriteria As String
 
  Select Case rating
      Case Is = "not rated"
          strCriteria = "rating_code = 'not rated'"
      Case Is = "C"
          strCriteria = "rating_code <> 'not rated'"
      Case Is = "B"
          strCriteria = "rating_code = 'B' or rating_code = 'A'"
      Case Is = "A"
          strCriteria = "rating_code = 'A'"
      Case Else
          MsgBox "Error in selection."
          Exit Sub
    End Select
 
    DoCmd.OpenForm "NewForm", , , strCriteria
    'or
    NewSubform.Form.Filter = strCriteria
End Sub 

[seagullino]

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!

[seagullino]

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.

[ChipR]

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?

[seagullino]

If the form is based on the table, you can simply filter the form.

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?

How are you allowing the user to select the rating? Something like a combo box on the form header?

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

[ChipR]

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?

[seagullino]

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.

[OldBirdman]

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.

[seagullino]

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 :)

[ChipR]

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.

[seagullino]

It sounds like your design requires that you display the query with results based on form values

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.

[OldBirdman]

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.

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

Maybe you could also post what you tried.

[seagullino]

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;

[seagullino]

any thoughts on why this isn't working?

:)

[OldBirdman]

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

"(([scored_bio].[rating_code])<=[Forms]![generator]![rating])

or even

"([scored_bio].[rating_code])<='B')

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!

[seagullino]

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!

[seagullino]

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.

[seagullino]

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!

[ChipR]

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"

[ChipR]

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")

[seagullino]

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!