By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,355 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

adding <> operators to IIF statement

P: 27
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
Jul 7 '09 #1
Share this Question
Share on Google+
22 Replies


P: 27
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 :)
Jul 8 '09 #2

Expert 100+
P: 1,287
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdShowRecords_Click(Cancel As Integer)
  2.   Dim strCriteria As String
  3.  
  4.   Select Case rating
  5.       Case Is = "not rated"
  6.           strCriteria = "rating_code = 'not rated'"
  7.       Case Is = "C"
  8.           strCriteria = "rating_code <> 'not rated'"
  9.       Case Is = "B"
  10.           strCriteria = "rating_code = 'B' or rating_code = 'A'"
  11.       Case Is = "A"
  12.           strCriteria = "rating_code = 'A'"
  13.       Case Else
  14.           MsgBox "Error in selection."
  15.           Exit Sub
  16.     End Select
  17.  
  18.     DoCmd.OpenForm "NewForm", , , strCriteria
  19.     'or
  20.     NewSubform.Form.Filter = strCriteria
  21. End Sub 
Jul 8 '09 #3

P: 27
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!
Jul 8 '09 #4

P: 27
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.
Jul 8 '09 #5

Expert 100+
P: 1,287
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?
Jul 8 '09 #6

P: 27
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.
Jul 8 '09 #7

Expert 100+
P: 1,287
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?
Jul 8 '09 #8

P: 27
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.
Jul 8 '09 #9

100+
P: 675
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.
Jul 8 '09 #10

P: 27
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 :)
Jul 8 '09 #11

Expert 100+
P: 1,287
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.
Jul 8 '09 #12

P: 27
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.
Jul 8 '09 #13

100+
P: 675
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.
Jul 8 '09 #14

P: 27
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;
Jul 8 '09 #15

P: 27
any thoughts on why this isn't working?

:)
Jul 9 '09 #16

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. "(([scored_bio].[rating_code])<=[Forms]![generator]![rating])
or even
Expand|Select|Wrap|Line Numbers
  1. "([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!
Jul 9 '09 #17

P: 27
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!
Jul 10 '09 #18

P: 27
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.
Jul 10 '09 #19

P: 27
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!
Jul 10 '09 #20

Expert 100+
P: 1,287
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"
Jul 10 '09 #21

Expert 100+
P: 1,287
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")
Jul 10 '09 #22

P: 27
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!
Jul 13 '09 #23

Post your reply

Sign in to post your reply or Sign up for a free account.