Nested IIF stament in Calculate field

[ptw78]

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.

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")))))

 

[SkipVought]

Hi,

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

since just Or "File Process" is TRUE

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Golom]

I assume that by this

Function: IIf([dbo_flat_wo]![status]="File Prep" Or "File Process" Or "File Received" ...

You mean

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.

 

[ptw78]

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.

 

[dhookom]

Actually, it might be easier to use:

Function: IIf([dbo_flat_wo]![status] IN ("File Prep","File Process","File Received" ...)

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

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

 

[ptw78]

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.

 

[MajP]

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

 

[ptw78]

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.

 

[ptw78]

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.

 

[ptw78]

Nevermind, looks like I've figured it out. Thanks for the help

 

[ptw78]

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?

 

[dhookom]

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

 

[ptw78]

Ok, thx for the tip.