switch vs nested iif

[ddskorupski]

ok. I need serious help here. I use Access 2007. I have what I thought should not be a complex query but I cannot get the query right. I have searched this forum for answers on select case and switch and honestly have no clue how to do this. here is what I need to happennot correct syntax...just so you get the gist)

IIF([derived_bu]="Primary Care" or "VR (Vaccines Research)", AND IIF ([real_work_flag]="Y", AND IIF ([Study_critical]="Y", 1,2,IIF([derived_bu]="specialty Care", AND IF ([level assignment]=1a or Reg or Viiv,1, IIF(derived_bu]="specialty care" AND IF ([level assignment]= 2a or Ph4, 2, else null

so anything that is primary care or vaccines research and has a flag of y for real work flag and study critical it sould return 1
all other bu's are 2 except for
specialty care: which if the level assignment is 1a, reg or viiv it shoud return 1 unless the level assignments are 2a and ph4..it will return 2. all other specialty cares should be blank

Can someone please help me figure out how to get this to work?

 

[rainman89]

Wow, what a mess!!

Try creating one at a time getting them to work then combine them - ONE at at time to see if you are getting what you want...

If you want to post up a sample database then you can and we can take a look at it to see if we can get it working with your SAMPLE data....

 

[ddskorupski]

thanks for the quick response. I did that. A query will not work, even one by one. It looks like a select case or switch could work but I have no clue how to build them. Any ideas?

 

[rainman89]

No im not saying create a seperate query, im saying create seperate IIF statements then combine them when you get a few working...

 

[ddskorupski]

That is what I did. only one iif will work

 

[rainman89]

Well I can see from your coding that you are missing a lot about what is needed for it to properly work.. Post up a sample and we can see if we can get it working for you

 

[ddskorupski]

As I said in my first post, I did not bother to type in the exact, proper code because I just don't have time. My nested iif statements are typed in properly.

As I said, I believe that Access 2007 believes this to be too complex. This is the reason I am looking for a select case or switch function.

Can you help me with either a switch function or a select case?

 

[MSAccessRookie]

ok. I need serious help here. I use Access 2007. I have what I thought should not be a complex query but I cannot get the query right. I have searched this forum for answers on select case and switch and honestly have no clue how to do this. here is what I need to happennot correct syntax...just so you get the gist)

IIF([derived_bu]="Primary Care" or "VR (Vaccines Research)", AND IIF ([real_work_flag]="Y", AND IIF ([Study_critical]="Y", 1,2,IIF([derived_bu]="specialty Care", AND IF ([level assignment]=1a or Reg or Viiv,1, IIF(derived_bu]="specialty care" AND IF ([level assignment]= 2a or Ph4, 2, else null

so anything that is primary care or vaccines research and has a flag of y for real work flag and study critical it sould return 1
all other bu's are 2 except for
specialty care: which if the level assignment is 1a, reg or viiv it shoud return 1 unless the level assignments are 2a and ph4..it will return 2. all other specialty cares should be blank

Can someone please help me figure out how to get this to work?

Your IIF Statements do not appear to follow the Basic Format of an IIf() statement, which is as follows:

IIf(Condition to Test, Value if TRUE, Value if FALSE)
​

The (Value if TRUE) and (Value if FALSE) clauses can each be replaced with an additional IIf() Statement to create the compound effect that you are looking for (See the examples below). I believe that the limit is around 12 levels, but I have rarely needed over 4. Whenever I get to 4, the Switch() method is usually a better choice for me.

IIf(Condition to Test, IIf(Condition to Test, Value if TRUE, Value if FALSE), Value if FALSE)

OR

IIf(Condition to Test, Value if TRUE, IIf(Condition to Test, Value if TRUE, Value if FALSE) )
​

Rewrite your IIf() Statements so that they meet the proper format

 

[stopher]

Why don't you just copy/paste the code you have tried?

Anyway, try:

IIf([derived_bu] In ("Primary Care","VR (Vaccines Research)"),IIf([real_work_flag]="Y" And [Study_critical]="Y",1,2),IIf([derived_bu]="specialty care" And [level assignment] In ("1a","Reg","Viiv"),1,IIf([level assignment] In ("2a","Ph4"),2,Null)))

hth
Chris

 

[MSAccessRookie]

As I said in my first post, I did not bother to type in the exact, proper code because I just don't have time. My nested iif statements are typed in properly.

As I said, I believe that Access 2007 believes this to be too complex. This is the reason I am looking for a select case or switch function.

Can you help me with either a switch function or a select case?

I would think that nothing would be faster than a straight up Cut and Paste, but nevertheless, when you leave out information, it is difficult for me to be able to assist you. I normally rely on the fact that the post contains the complete information that I need, and when it does not, I am not always able to get the answer that is being sought.

In other words, taking a little more time up front might lessen the number of posts required to determine the best method to assist you.

 

[boblarson]

As I said in my first post, I did not bother to type in the exact, proper code because I just don't have time.

Copy the code from your database and paste it here. As MSR has said, we can't help you if you don't give us the information.

For example, if you go to a doctor and then say "Doc, I have a pain and it is sort of like a radiation from my neck to the right side of my hand." He will start asking questions about your neck to your hand, but then you say, "No, Doc I didn't mean that. That was just an example. I really meant that my hip to my left foot hurts but it would have taken longer to explain that to you so I used another example."

What do you think the Doc will say to you?

 

[Galaxiom]

There are so many errors in your posted code.

Where there are two conditions being tested you cannot OR the comparison:
IIF([derived_bu]="Primary Care" or "VR (Vaccines Research)".....

It looks like this:
IIF([derived_bu]="Primary Care" or [derived_bu]="VR (Vaccines Research)"....

This is invalid:
... AND IIF ([real_work_flag]="Y", ....

You need to nest the IIF as shown by MSAccessRookie

You cannot use IF THEN ELSE statements in a query.

Several of your comparison strings are not in quotes.

Your design could use a lot more normalisation so you store codes rather than strings like "Primary Care".

 

[ddskorupski]

Thank you for the posts. but I am not looking for help with nested iifs. I am looking for help in create a select case or switch function. This is why I gave a description of what I am trying to achieve.

Again, I do not want a nested iif to work so I will not be posting my statements. I am looking for help with select cases and/or switch functions.

 

[MSAccessRookie]

Thank you for the posts. but I am not looking for help with nested iifs. I am looking for help in create a select case or switch function. This is why I gave a description of what I am trying to achieve.

Again, I do not want a nested iif to work so I will not be posting my statements. I am looking for help with select cases and/or switch functions.

I believe that you may have misinterpreted the reason that I and the others have asked to view the existing code. In my experience, the easiest way for me to help you would be to see the existing IIf() Statements so that I could break them apart into the appropiate Switch() Statements.

 

[boblarson]

Again, I do not want a nested iif to work so I will not be posting my statements. I am looking for help with select cases and/or switch functions.

Post what you have now, so we can help get you set up. If you say, "I will not be posting my statements" and you do not, then most likely we cannot help you. Do you want help? Then post what you currently have.

 

[boblarson]

Otherwise, look at the help file as it tells you how to use SWITCH just like we would.