iif statement with or's

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
PeterBaileyUkAsked:
Who is Participating?
 
GRayLConnect With a Mentor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
GRayLCommented:
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
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
JezWaltersCommented:
Ah ... perhaps not!  :-(  But boag2000's idea might help you at least put all the logic in one place.
0
 
JezWaltersCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
PeterBaileyUkAuthor Commented:
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
 
GRayLCommented:
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
 
PeterBaileyUkAuthor Commented:
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
 
GRayLCommented:
Thanks, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.