Solved

iif statement with or's

Posted on 2011-03-23
13
428 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
[X]
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
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

729 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