Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Nested IIF stament in Calculate field

Status
Not open for further replies.

ptw78

Technical User
Mar 5, 2009
155
US
Any help on why this nested IIF statement isn't working properly? There aren't any errors. It just returns "Processing" for every field. I've checked spelling, etc. but no luck.

Code:
Function: IIf([dbo_flat_wo]![status]="File Prep" Or "File Process" Or "File Received" Or "File Rev Miss/I" Or "File Setup" Or "Package Sent" Or "Recommendation" Or "Rev Miss/Incmpl","Processing",IIf([dbo_flat_wo]![status]="Complet Pkg MOD" Or "Completing File" Or "File to U/W or" Or "File to UW/INV" Or "Negotiator Revw","Underwriting",IIf([dbo_flat_wo]![status]="Approval" Or "Approved" Or "Final Payments" Or "Stip Requiremnt","Approved",IIf([dbo_flat_wo]![status]="Docs to Borrow" Or "Docs to Borrowr" Or "Mod Sent To Mtr","Closing",IIf([dbo_flat_wo]![status]="Admin Pending" Or "Ad-Pending" Or "Docs Returned" Or "Pend Inv Req" Or "Receipt Of Mod" Or "Receipt Mod","Booking","N/A")))))
 



Hi,

You must have the equality expression in each OR...

since just Or "File Process" is TRUE

Skip,
[sub]
[glasses]Just traded in my old subtlety...
for a NUANCE![tongue][/sub]
 
I assume that by this
Code:
Function: IIf([dbo_flat_wo]![status]="File Prep" Or "File Process" Or "File Received" ...
You mean
Code:
Function: IIf([dbo_flat_wo]![status]="File Prep" 
           Or [red][dbo_flat_wo]![status]=[/red]"File Process" 
           Or [red][dbo_flat_wo]![status]=[/red]"File Received" ...
Your version will not make the comparison between the field and the literals. It will just attempt to interpret the literal as a logical value. Since the conversion will probably convert to a non-zero value, the result is always TRUE and "Processing" is returned.

Similar comments apply to the logical tests in the other IIF statements.
 
Ok got it, Thanks. Question though. What do you do if the IIF statement is too long? I've never ran into that issue. And I want to make it all one field instead of two.
 
Excuse my previous post. I should have suggested none of the solutions are ideal. Your expression contains too many hard-code values. You should use tables to model this. Data should be in your tables and not your expressions. I would expect to see a status table with a column for "Function" or maybe a better name.

At the very least, I woul create a small user-defined function so your business logic is at least encapsulated somewhere that can be more easily maintained.

Duane
Hook'D on Access
MS Access MVP
 
Ok, I'm a little confused on how I would do that. I'm guessing you mean to make a table, call it "Function" or whatever name. Then what? I'm a little confused there.
 
tblRefFunction

statusID strFunction
file Prep Processing
File Process Processing
.
.
Rev Miss/Incmp Processing
.
Complet Pkg MOD Underwriting
.
Receipt Mod Booking

now do an inner join of
[dbo_flat_wo]![status] on statusID and pull in strFunction
status
 
OK so tblRefFunction is the name of the table correct?
-statusID and strFunction are the name of two fields in
the table????
-and put the data in each corresponding column???

never done an inner join, so I'll have to look into that.
 
Is it possible to write this in the design view of the query? I'm not very familiar w/the SQL view and what I have in there now has a lot of SQL code in it that I dont want to mess around with.
 
Nevermind, looks like I've figured it out. Thanks for the help
 
Doing this brings me to another question.
If I have a field in a query where I'm going to put a lot of things in the criteria, would doing an inner join like this be the right way to go? And if so is it a similar process as doing it with a calculated field?
 
There is a good page Data belongs in your tables -- not in your code that explains better than I can regarding my suggestion.

I don't understand what you mean by "a lot of things in the criteria". If you mean a criteria containing a lot of hard-coded values then you might be better off using tables and joins to provide the filtering.

When you create applications, it is always good practice to assume the hard-coded list of values [red]will change[/red]. You don't want to maintain lists of values in expressions. You do want to maintain lists of values in tables.

Duane
Hook'D on Access
MS Access MVP
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top