Solved

iif statement with or's

Posted on 2011-03-23
13
385 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 17

Expert Comment

by:JezWalters
Comment Utility
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
Comment Utility
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
Comment Utility
Ah ... perhaps not!  :-(  But boag2000's idea might help you at least put all the logic in one place.
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 17

Expert Comment

by:JezWalters
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, glad to help.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

728 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now