Using WHERE, OR and IIF with UPDATE

[hemant86]

Hi All I have the following table (sample)

[TABLE="width: 405"]
<tbody>[TR]
[TD]HOST Function[/TD]
[TD] [/TD]
[TD]SWC Consumption[/TD]
[TD] [/TD]
[TD]RTB/CTB for TM1[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS ITO[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]HTS SWD[/TD]
[TD] [/TD]
[TD]NON SWC[/TD]
[TD][/TD]
[TD]?[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]

I want to replicate the below excel formula in a query to populate data in column RTB/CTB for TM1

=IF(AD11="HTS SWD",IF(LEFT(E11,3)="Run","Run the Bank - Non-Regulatory",IF(C11="SWC","Change the Bank - Group Sys core code","Change the Bank - Regional Systems")),E11)

Note:AD11 - HOST Function, C11- SWC Consumption

Thanks in Advance..

 

[stumac]

The syntax in a query is very similar - IF changes to IIF and the cell address changes to the Field name. In your example you don't say what field E11 would equate to.

MyField:IIF([Host Function]="HTS SWD",IIF(LEFT([E11 Field],3)="Run","Run the Bank - Non-Regulatory",IIF([SWC Consumption] ="SWC","Change the Bank - Group Sys core code","Change the Bank - Regional Systems")),[E11 Field])

Also if you have multiple tables in the query and the field names being used exist in more than one then you need to include that in the field name: [Table name].[Field Name]

 

[hemant86]

Thanks...that really helped