Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

iif statement with or's

Posted on 2011-03-23
13
412 Views
Last Modified: 2012-06-27
I have:

VariantChange: IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False))=True,True,False)

it works fine

want to add another OR for this statement:
SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=true

having trouble adding them together
0
Comment
Question by:PeterBaileyUk
  • 5
  • 4
  • 3
  • +1
13 Comments
 

Author Comment

by:PeterBaileyUk
ID: 35200783
tried this but didnt work
IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or
 (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False) or (SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=true))=True,True,False)

Open in new window

0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35200825
I am sure an Expert can help you with this directly.

For me, if I need anything more than a basic IIF()
IIF(Something1=Something2,"X","Y")

I tend to build a function.
With a function:
You can use it anywhere in your app by putting it in a Module, and "Calling" it. (not by typing it in over and over again)
You can put comments in the code
You can have error handling
The values are not hardcoded.
It is easier to troubleshoot if something goes wrong
...

;-)

JeffCoachman
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35200894
I tested opening and closing parentheses and the statements separated by OR are complete - they are not nested.  You should be able to just add the third condition separated by an OR:

VariantChange: IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False))=True,True,False) OR
SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=true
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 17

Expert Comment

by:JezWalters
ID: 35200972
You can almost certainly express your query more succinctly if you build it using the SQL View.  What SQL do you have currently in SQL View?
0
 

Author Comment

by:PeterBaileyUk
ID: 35201243
sqlview:

SELECT QryClientDifferencesLogic.abiCode, AbiCurrentCodes.MODEL_DESCRIPTION, AbiCodesPrevious.MODEL_DESCRIPTION AS [Previous MODEL DESCRIPTION], AbiCurrentCodes.MANUFACTURED_FROM, AbiCodesPrevious.MANUFACTURED_FROM AS [Previous MANUFACTURED FROM], AbiCurrentCodes.MANUFACTURED_TO, AbiCodesPrevious.MANUFACTURED_TO AS [Previous MANUFACTURED TO], AbiCurrentCodes.SERIES, AbiCodesPrevious.SERIES AS [Previous SERIES], AbiCurrentCodes.ENGINE_CC, AbiCodesPrevious.ENGINE_CC AS [Previous ENGINE CC], AbiCurrentCodes.abiDOORS, AbiCodesPrevious.abiDOORS AS [Previous abiDOORS], AbiCurrentCodes.BODY_TYPE, AbiCodesPrevious.BODY_TYPE AS [Previous BODY TYPE], AbiCurrentCodes.ENGINE_TYPE, AbiCodesPrevious.ENGINE_TYPE AS [Previous ENGINE TYPE], AbiCurrentCodes.TRANSMISSION_TYPE, AbiCodesPrevious.TRANSMISSION_TYPE AS [Previous TRANSMISSION TYPE], AbiCurrentCodes.Gross_Kg, AbiCodesPrevious.Gross_Kg AS [Previous Gross Kg], QryClientDifferencesLogic.ModelDescdiff, QryClientDifferencesLogic.ManfFromdiff, QryClientDifferencesLogic.ManfTodiff, QryClientDifferencesLogic.Seriesdiff, QryClientDifferencesLogic.EngineCCdiff, QryClientDifferencesLogic.AbiDoorsdiff, QryClientDifferencesLogic.AbiBodydiff, QryClientDifferencesLogic.EngineTypediff, QryClientDifferencesLogic.AbiTransmissiondiff, QryClientDifferencesLogic.Grosskgdiff, Not IsNull([MatchString]) AS AbiMatched, QryAbiMatched.MatchString, SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]) AS searchwordbyword, IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False) Or (SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=True)=True),True,False) AS new
FROM ((QryClientDifferencesLogic LEFT JOIN AbiCurrentCodes ON QryClientDifferencesLogic.abiCode = AbiCurrentCodes.abiCode) LEFT JOIN AbiCodesPrevious ON QryClientDifferencesLogic.abiCode = AbiCodesPrevious.abiCode) LEFT JOIN QryAbiMatched ON QryClientDifferencesLogic.abiCode = QryAbiMatched.MatchString
WHERE (((Not IsNull([MatchString]))=True) AND ((SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]))=True) AND (([AbiCodesPrevious]![MAKE_DESCRIPTION]) Not Like "Dump code") AND (([AbiCurrentCodes]![MAKE_DESCRIPTION]) Not Like "Dump code") AND ((IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False) Or (SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=True)=True),True,False))=True))
ORDER BY QryClientDifferencesLogic.abiCode;
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35201280
Ah ... perhaps not!  :-(  But boag2000's idea might help you at least put all the logic in one place.
0
 
LVL 17

Expert Comment

by:JezWalters
ID: 35201313
What I mean to say is, you could combine your Soundex(), IsValid() and SearchbyWord() functions since they are all being passed MODEL_DESCRIPTION values.
0
 

Author Comment

by:PeterBaileyUk
ID: 35201376
now u can see why!

tis worked
VariantChange: IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False))=True,True,False)

not sure whyadding this didnt
or (SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=true)
0
 

Author Comment

by:PeterBaileyUk
ID: 35201395
the attached returns the correct rows but i need the results ord in a column as I want the logic result to be used elsewhere
SELECT QryClientDifferencesLogic.abiCode, AbiCurrentCodes.MODEL_DESCRIPTION, AbiCodesPrevious.MODEL_DESCRIPTION AS [Previous MODEL DESCRIPTION], AbiCurrentCodes.MANUFACTURED_FROM, AbiCodesPrevious.MANUFACTURED_FROM AS [Previous MANUFACTURED FROM], AbiCurrentCodes.MANUFACTURED_TO, AbiCodesPrevious.MANUFACTURED_TO AS [Previous MANUFACTURED TO], AbiCurrentCodes.SERIES, AbiCodesPrevious.SERIES AS [Previous SERIES], AbiCurrentCodes.ENGINE_CC, AbiCodesPrevious.ENGINE_CC AS [Previous ENGINE CC], AbiCurrentCodes.abiDOORS, AbiCodesPrevious.abiDOORS AS [Previous abiDOORS], AbiCurrentCodes.BODY_TYPE, AbiCodesPrevious.BODY_TYPE AS [Previous BODY TYPE], AbiCurrentCodes.ENGINE_TYPE, AbiCodesPrevious.ENGINE_TYPE AS [Previous ENGINE TYPE], AbiCurrentCodes.TRANSMISSION_TYPE, AbiCodesPrevious.TRANSMISSION_TYPE AS [Previous TRANSMISSION TYPE], AbiCurrentCodes.Gross_Kg, AbiCodesPrevious.Gross_Kg AS [Previous Gross Kg], QryClientDifferencesLogic.ModelDescdiff, QryClientDifferencesLogic.ManfFromdiff, QryClientDifferencesLogic.ManfTodiff, QryClientDifferencesLogic.Seriesdiff, QryClientDifferencesLogic.EngineCCdiff, QryClientDifferencesLogic.AbiDoorsdiff, QryClientDifferencesLogic.AbiBodydiff, QryClientDifferencesLogic.EngineTypediff, QryClientDifferencesLogic.AbiTransmissiondiff, QryClientDifferencesLogic.Grosskgdiff, Not IsNull([MatchString]) AS AbiMatched, QryAbiMatched.MatchString, SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]) AS searchwordbyword, SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]) AS Expr1, IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False))=True,True,False) AS VariantChange
FROM ((QryClientDifferencesLogic LEFT JOIN AbiCurrentCodes ON QryClientDifferencesLogic.abiCode = AbiCurrentCodes.abiCode) LEFT JOIN AbiCodesPrevious ON QryClientDifferencesLogic.abiCode = AbiCodesPrevious.abiCode) LEFT JOIN QryAbiMatched ON QryClientDifferencesLogic.abiCode = QryAbiMatched.MatchString
WHERE (((Not IsNull([MatchString]))=True) AND ((SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]))=True) AND (([AbiCodesPrevious]![MAKE_DESCRIPTION]) Not Like "Dump code") AND (([AbiCurrentCodes]![MAKE_DESCRIPTION]) Not Like "Dump code") AND ((SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]))=True) AND ((IIf((IIf(Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True) Or (IIf((isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True),True,False))=True,True,False))=True))
ORDER BY QryClientDifferencesLogic.abiCode;

Open in new window

0
 
LVL 44

Expert Comment

by:GRayL
ID: 35201439
By matching opening and closing parentheses, one can determine whether any of the components of calculation are nested.  By my reckoning, each component of New in the SELECT clause separated from the other by OR stood alone.  There adding the third should be done as I suggested.  Did you try it in the Query Designer?
0
 

Author Comment

by:PeterBaileyUk
ID: 35201521
I separated it out in the query grid and now it returns the correct rows but i need a new column that is an OR of all the results in excel i would do OR(exp1,exp2,exp3). here not sure it looked ok to me as the two first sets of logic worked and are separated out and I thought just another OR witht the new expression would work

removing the iifs to simplify

Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False)=True

isvalid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION])=True

SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION])=true

so i need a column that is the OR of the 3 above.
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 35201712
So if the Soundex, IsValid and SearchByWord functions each return a Boolean value, then try this:

Soundex([abicurrentcodes].[MODEL_DESCRIPTION])<>Soundex([abicodesprevious].[MODEL_DESCRIPTION]),True,False) OR
IsValid([abicodesPrevious].[MODEL_DESCRIPTION])=False) And (isvalid([abicurrentcodes].[MODEL_DESCRIPTION]) OR
SearchbyWord([abicurrentcodes].[MODEL_DESCRIPTION],[abicodesprevious].[MODEL_DESCRIPTION]) AS VariantChg

0
 
LVL 44

Expert Comment

by:GRayL
ID: 35202075
Thanks, glad to help.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Single Step Macro 4 41
table joins in qry 17 61
Sum in Split Form 17 27
Access - Rounding an average in a report 3 14
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question