IIF AND Statements (1 Viewer)

Omo_ekun

New member
Local time
Today, 04:39
Joined
Nov 15, 2017
Messages
5
Hi all,

I need help with the statement below (Access 2007-2010 version). I'm trying to figure out the reason for the error :banghead::

Country Rating: iif([employment]=3 and [income]=2 and [population]=1 and [age]=2, "High", iif([employment]=3 and [income]=1 and [population]=1 and [age]=3, "Low"))

When I ran the query, I got the "You may have entered an invalid comma or omitted quotation marks. for example, if the default value..."
 

plog

Banishment Pending
Local time
Today, 06:39
Joined
May 11, 2011
Messages
11,613
That expressions looks syntatically correct. Are you sure its with that and not another part of the SQL?
 

MrHans

Registered User
Local time
Today, 12:39
Joined
Jul 27, 2015
Messages
147
Are we sure that the 'else' statements here are optional?

You have 2 iif statements and only 2 then statements, I think you need at least 1 more else statement.

Iif(x = y, high, iif(a = b, low, ""), "")
 

Omo_ekun

New member
Local time
Today, 04:39
Joined
Nov 15, 2017
Messages
5
@ MrHans: I have about 20 scenarios.
@ plog: I wrote the query to see if it would work before writing the entire 20 scenarios.
 

isladogs

MVP / VIP
Local time
Today, 11:39
Joined
Jan 14, 2017
Messages
18,186
Posts crossed but this is what I'd already written

To reinforce the comment by Mr Hans,

a) what happens if neither of those conditions applies
e.g. ([employment]=3 and [income]=2 and [population]=1 and [age]=3

You would need an outcome for other such conditions e.g.

Code:
Country Rating: IIf([employment]=3 and [income]=2 and [population]=1 and [age]=2, "High", IIf([employment]=3 and [income]=1 and [population]=1 and [age]=3, "Low", "SOMETHING ELSE"))

b)if the only 2 alternatives are High & Low then all you need is:

Code:
Country Rating: IIf([employment]=3 and [income]=2 and [population]=1 and [age]=2, "High", "Low")

------------------------------------------------
Following on from your last post:
However if you have 20 scenarios, nested IIf statements are not the answer.
The code would be a nightmare to write / edit etc

Select Case statements MIGHT work.
A UDF would work.
However I would suggest creating a table with each of the possible scenarios & the output for each. Then reference that.
 

MrHans

Registered User
Local time
Today, 12:39
Joined
Jul 27, 2015
Messages
147
To increase readability and prevent missing parameters, I would not use IIF for 20 scenarios.

Why not just 20 If... Then... End if statements?
Would make things a lot clear in my opinion.
 

plog

Banishment Pending
Local time
Today, 06:39
Joined
May 11, 2011
Messages
11,613
I have about 20 scenarios.

Then you shouldn't (and probably can't) do it like that. Instead you should build a custom function. I assume all 20 scenarios are based on the 4 fields you used above. In the query you would simply have this:

CountryRating: get_CountryRating([employment], [income], [population], [age])

Then in a module, you would build a custom function. Below is psuedo code, actual working code is on you:

Code:
Function get_CountryRating(in_employment, in_income, in_population, in_age) AS String
    ' function to determine country rating

ret= "Error"    
  ' return value, by default will show error

If ( ...insert logic for 1st scenario here...) Then ret = "...insert result of scenario 1 here..."
If ( ...insert logic for 2st scenario here...) Then ret = "...insert result of scenario 2 here..."
...
...
If ( ...insert logic for 20th scenario here...) Then ret = "...insert result of scenario 20 here..."


get_CountryRating = ret

End Function

That's how you should accomplish this, not by jamming a ton of logic into a one line exrpession inside the query.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:39
Joined
Feb 19, 2002
Messages
42,981
Can the complicated nested Ifs. Create a table with all the combinations and the result you want returned. Use a left join to that table. If your query returns null, you'll know that you have a missing condition. You can create a form to manage the table and if it is something that the user can be responsible for, you are out of the picture. If a programmer needs to control the table contents, then you can do it but it will be significantly easier to manage and test than using an SQL statement that no one can read.
 

Omo_ekun

New member
Local time
Today, 04:39
Joined
Nov 15, 2017
Messages
5
Thank you all for your replies.
@ Pat Hartman, I am interested in your idea. How will I do the left join?

P.S I am a newbie with access.
 

Omo_ekun

New member
Local time
Today, 04:39
Joined
Nov 15, 2017
Messages
5
I created 2 tables and did a join: one for the scenarios and the second one for the results. I started with 4 scenarios. The query displayed 16 permutation for the 4 scenarios instead of just 4 scenarios with the results.
 

isladogs

MVP / VIP
Local time
Today, 11:39
Joined
Jan 14, 2017
Messages
18,186
In your query are the 2 tables are actually joined?
If not you have what is called a cartesian join which effectively means you get 4 records for each record in this case i.e. 16

This shows an INNER join - records only shown if they exist in both tables



The image below shows a LEFT join in the query designer - note the arrow direction. Records shown if they exist in the LEFT table



You can also do a RIGHT join

If you have the correct join and still get multiple records change the query design setting Unique Values = Yes
This is the same as SELECT DISTINCT in query SQL
 

Attachments

  • Capture.PNG
    Capture.PNG
    6.6 KB · Views: 152
  • CaptureINNER.PNG
    CaptureINNER.PNG
    6.5 KB · Views: 152

jdraw

Super Moderator
Staff member
Local time
Today, 07:39
Joined
Jan 23, 2006
Messages
15,364
omo_ekun,

Don't overlook Google or Bing to help with your research.

Lots of SQL info, joins, examples.

It is always helpful to readers and you if you provide a clear description of what you are trying to do. You have shown us how you have tried to solve something, but have not clearly provided the "what".
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:39
Joined
Feb 19, 2002
Messages
42,981
Your joins will be on multiple fields. Draw a separate join line between each pair of fields

tbl1.Employement -> tbl2.Employment
tbl1.Income -> tbl2.Income
tbl1.Population -> tbl2.Population
tbl1.Age -> tbl2.Age
 

Users who are viewing this thread

Top Bottom