IIF Expression with Multiple Conditions

[mba_110]

Dear All,

I am trying to make following expression in access i succeeded in excel but unable to get correct result in Access 2010 query.

Can anyone help me please?

My excel formulas was
=IF(ISNUMBER(AB13),IF(G13="Egyptian",AB13*1800)+IF(G13="Philippines",AB13*3500)+IF(G13="Sudan",AB13*2000)+IF(G13="Nepal",AB13*2500)+IF(G13="Indonesia",AB13*2500)+IF(G13="Bangladesh",AB13*2500)+IF(G13="Indian",AB13*2500)+IF(G13="Pakistan",AB13*2500),ROUND(,0))


My access attempt is
Approx_Amt: IIf(IsNumeric([Accrued_Tkt]),IIf([Employees]![Nationality]="Egypt",[Accrued_Tkt]*1800)+IIf([Employees]![Nationality]="India",[Accrued_Tkt]*2500)+IIf([Employees]![Nationality]="Sudan",[Accrued_Tkt]*2000)+IIf([Employees]![Nationality]="Nepal",[Accrued_Tkt]*2500)+IIf([Employees]![Nationality]="Indonesia",[Accrued_Tkt]*2500)+IIf([Employees]![Nationality]="Bangladesh",[Accrued_Tkt]*2000)+IIf([Employees]![Nationality]="Morocco",[Accrued_Tkt]*2500)+IIf([Employees]![Nationality]="Pakistan",[Accrued_Tkt]*2500)+IIf([Employees]![Nationality]="Philippines",[Accrued_Tkt]*3500),Round(,0))


thanks & regards,

MBA

 

[Darren Bartrup]

Rather than use an IIF statement create another table to hold the nationality and multiplier.

As a simplified example - if you have two tables. The first table, called Employees, contains the Nationality and Accrued_Tkt amount. The second table, called LookUp, contains the nationality and multiplier amount.

This SQL will multiply the Employees.Accrued_Tkt by the LookUp.Multiplier:

SELECT  Employees.Nationality, Accrued_Tkt, Accrued_Tk*Multiplier
FROM    Employees LEFT JOIN Lookup ON Employees.Nationality = Lookup.Nationality

Excel Workbook
	A	B	C
1	Employees	*	*
2	Nationality	Accrued_Tkt	*
3	India	1	*
4	Sudan	2	*
5	Egypt	3	*
6	*	*	*
7	LookUp	*	*
8	Nationality	Multiplier	*
9	Egypt	1800	*
10	India	2500	*
11	Sudan	2000	*
12	*	*	*
13	Query1	*	*
14	Nationality	Accrued_Tkt	Expr1
15	India	1	2500
16	Sudan	2	4000
17	Egypt	3	5400
Sheet

 

[mba_110]

Dear Darren,

Many thanks for your help.

However i dont like so many tables and queries in my first project database at early stages because there will be many as far as i move forward, hence i have made only one table called lookup with Nationality and multiplier amount as you mentioned.

Actually let me explain you a little i am trying to calculate [Approx_Amt] amount of [Contract]![Ticket] based on their [Accrued_Tkt] numbers, but as per their [Employees]![Nationality]'s price.

Please i have following tables which contains the field that i need to get result from.

Tables Field
Contract [Ticket]
Employees [Nationality]

Query Field
AirticketQuery [Accrued_Tkt]
[Approx_Amt] - This is result field.

As per your advise i have created lookup table this will help me in this expression as well as any other future requirement that need to be listed, but please can you provide me the code based on above or expression that i original asked for?

thanks & regards,

MBA