iIF using Multiple conditions

[djossh]

Hi, How can I combine this two iIF formula into one..

IIf( [LeaveTypeID] =1 AND ([MEPNumber]>200,0,[BasicSalary])
IIf( [LeaveTypeID] =1 AND ([MEPNumber]<200,0,[BasicSalary]/30*21)


The formula above will give me a result of "0" if the "LeavetypeID" is not equal to 1 or should I say any number except 1.

But if the "LeavetypeID" is other than "1" it will check the next argument, which is "MEPNumber".... if it is >200 it should give me the value of "BasicSalary" ....and if "MEPNUmber" is <200 it should give me "BasicSalary / 30*21"

 

[Severin]

IIf( [LeaveTypeID] =1,0,IIf([MEPNumber]>200,[BasicSalary],[BasicSalary]/30*21))

 

[djossh]

IIf( [LeaveTypeID] =1,0,IIf([MEPNumber]>200,[BasicSalary],[BasicSalary]/30*21))

Thanks.. that was working perfectly.. But I notice one thing.. if the "LeaveTypeID" is null... it also gives me the the results.. any other way to include the null value?? Sorry fot asking too much.. I just figured it out now.. Thank you so much...

 

[Severin]

are you all set!~)

 

[djossh]

are you all set!~)

nope... still struggling.. what I meant on my post "I just figured it out now" is the error I encountered with the null values in "LeaveTypeID".. I tried this but stilll not working.. please help.. Thanks

=IIf([LeaveTypeID]=1 Or [LeaveTypeID]="",0,IIf([MEPNumber]<200,[BasicSalary],[BasicSalary]/30*21))

 

[Severin]

Does this work?~)

=IIf([LeaveTypeID]=1 Or IsNull([LeaveTypeID]),0,IIf([MEPNumber]<200,[BasicSalary],[BasicSalary]/30*21))

 

[djossh]

Does this work?~)

=IIf([LeaveTypeID]=1 Or IsNull([LeaveTypeID]),0,IIf([MEPNumber]<200,[BasicSalary],[BasicSalary]/30*21))

PERFECT!!!!! THANK YOU!!! I actually tried that at first but without carriage returns...anyway.. Thank you so much.. I really appreciate your help.. GOD Bless.....

 

[Severin]

Glad to help!~)

 

[Freshman]

Sorry - can I latch on to this post with another multiple IIF question:

Need for [MarStatus] to be:
0 if MSt = "Single"
1 if MSt = "Married"
2 if MSt = "Divorced"
3 if Mst = "Widowed"

Can do it in code but batteling with Query syntax

This does not work:

MarStatus: IIf([MSt]="Single",0 or [MSt]="Married",1 or [MSt] = "Divorced", 2 or [MSt] = "Widowed",3)


Thanks

 

[Severin]

You have to nest the iif's.. Something like this:

MarStatus: IIf([MSt]="Single",0,iif([MSt]="Married",1,iif([MSt] = "Divorced", 2,iif([MSt] = "Widowed",3))))

Syntax not testet!~)

 

[Freshman]

Worked like a bomb - thanks

 

[GregRun]

I see you already have the answer but with that kind of statement it might be easier for you if you use a Switch statement:

Switch([MSt]="Single",0,[MSt]="Married",1,[MSt]="Divorced",2,[MSt]="Widowed",3)

Look at the help for the description of the syntax.

 

[Freshman]

Thanks Greg - I did not know about the Switch function - sure I'll use it in future.

 

[Jmkrull]

I have a similar issue.

I have an iif statement:

IIf([plant]="MULTIPLE" And [Discrepancy Code] Like "A*",IIf([tCC_Master].[BU]="Neurovascular","1188","1139"),Nz([plant],IIf([tCC_Master].[BU]="Neurovascular","1188","1139"))) AS DC

and the null Neurovascular records are still returning 1139 in plant, but they should be 1188.

Do I need to adjust this? Thanks.

 

[MarkK]

It looks to me like your IIF() function has too many parameters.

IIf(
1.   [plant]="MULTIPLE" And [Discrepancy Code] Like "A*",
2.   IIf(
      [tCC_Master].[BU]="Neurovascular",
      "1188",
      "1139"),
3.   Nz([plant],
4.   IIf(
      [tCC_Master].[BU]="Neurovascular",
      "1188",
      "1139")
)) AS DC

You appear to passing in 4 parameters, but the function only takes 3. And too many closing parenthesis???

Or am I missing something?

 

[MarkK]

Oh, wait, that IIF() function in 4. is actually the return value of your Nz() function, right? So I would include that test in the original expression, like consider . . .

IIf(
1.   IsNull(plant) OR (plant="MULTIPLE" And [Discrepancy Code] Like "A*"),
2.   IIf(
      tCC_Master.BU="Neurovascular",
      "1188",
      "1139"),
3.   plant)
) As DC

Does that work?

 

[Jmkrull]

It looks to me like your IIF() function has too many parameters.

IIf(
1.   [plant]="MULTIPLE" And [Discrepancy Code] Like "A*",
2.   IIf(
      [tCC_Master].[BU]="Neurovascular",
      "1188",
      "1139"),
3.   Nz([plant],
4.   IIf(
      [tCC_Master].[BU]="Neurovascular",
      "1188",
      "1139")
)) AS DC

You appear to passing in 4 parameters, but the function only takes 3. And too many closing parenthesis???

Or am I missing something?

Strange is I went and took lunch, came back, tried it again and it worked. I don't know what's up. Thanks for the reply though!

 

[aamir.shehzad9]

Hi,
I want to know a query or function here and hope so someone would help me in the situation;

I have multiple tables and the data of multiple data coming to in a final table, Now the situation is here,

Understand tables as Table A, B, C, D, E, F,...
final table is "Final table"
how I formulate that If data comes from table A then formula field say "AAA" if data comes from table B field say "BBB" just like..
I don't any formula which is based on table name, means tables names are not contained in table, and I have the tables on linked bases and cannot apply anything in those table to referencing for formula or function...
Can anyone help me here...
I'm less than a beginner

 

[aamir.shehzad9]

Look let me try to explain here again
I've 11 tables with the names likewise Table A, Table B, Table C, Table D, and so...
data from these tables need in "Final Data" table.
Now how would I no when all the table will merge with there data in "Final Data" table?
Solution I do here is,,
If Data of table A is referencing in "Final Data" table so a field will fill with the table reference with defined parameters or name... typing some how example again below;

If Data belongs to, Table A then AAA, if data belongs to Table B then BBB

just like in the access field indicate with value in field ,,,

 

[JHB]

What is the idea of inserting the data from 11 tables into 1 table??
A way to do that is using a union query!