Need help with Query formula

[krymer]

I have a database for converting settlement instruction formats between one system and another. When this was created for me, one of the columns that I needed to be in the query was not included. The table was created from an imported Excel file which is how the report is run. I need to have a formula where if the value in a column called PAY_INDICATOR is an "T" then to return the value in a column called PAY_ACCRU, if the PAY _INDICATOR column has an "O", then return the value that is in column PAY_BEN_PARTY. Otherwise, if there are no values provided in either of the columns the query would search depending on if we have a P or a Q, columns: (PAY_ACCRU or PAY_BEN_PARTY), then return the value "NULL". We have the below formula in the query already, however when we run the query it does not display the results or the column at all.

ULTBEN_ADD_LINE1(T58/T59): Iif([Banks_Comparison_Results].[Pay_Indicator]="T",[Banks_Comparison_Results].[PAY_ACCOUNT],[PAY_BEN_PARTY])



I dont see the criteria where if the value in column PAY_INDICATOR is an "O" how this is evaluated, or if no values are present in both columns display the word "NULL" Any help would be very appreciated! Thanks

[NTC]

ULTBEN_ADD_LINE1(T58/T59): Iif([Banks_Comparison_Results].[Pay_Indicator]="T",[Banks_Comparison_Results].PAY_ACCRU,(Iif([Banks_Comparison_Results].[Pay_Indicator]="O",[Banks_Comparison_Results].PAY_BEN_PARTY,"")))

[krymer]

Thanks for the reply, but I also need the query to say "NULL" if there is not a value in whatever is the lookup column. For instance if the column has a "P" and the corresponding column that it looks up does not have a value in it, I need it to display the word NULL, how would this look in the formula? Thanks very much

[NTC]

ULTBEN_ADD_LINE1(T58/T59): Iif([Banks_Comparison_Results].[Pay_Indicator]="T",[Banks_Comparison_Results].PAY_ACCRU,(Iif([Banks_Comparison_Results].[Pay_Indicator]="O",[Banks_Comparison_Results].PAY_BEN_PARTY,"Null")))


Of course it appears you are mixing numerals with text in the same field...which could cause issues in any additional math that may be needed with this calculated column down the road.

b.t.w. - just aircode, have not tried.
also: nested Iif statements are challenging. another approach is to put each iif statement in its own calculated field - - and then make a final calculated field conditional upon the "pre" calculated fields. one nice thing about this approach is when you run the query you can visually inspect each iif statements results by itself column by column to see if any errors occur.

hope this helps.

[krymer]

Thanks very much for your help! Just curious, how would you put each iif statement in its own calculated field? What would that look like, thanks!

[NTC]

a 2 level nesting of IIF isn't so bad; but when people get into more then it really can be helpful to see the intermediate results. You would simply make side by side calculated values with 1 result each - - and then generally you need to make a final one too. In your case it would start out something like this:

Tfind: Iif([Banks_Comparison_Results].[Pay_Indicator]="T",[Banks_Comparison_Results].PAY_ACCRU, "X")

Ofind: Iif([Banks_Comparison_Results].[Pay_Indicator]="O",[Banks_Comparison_Results].PAY_BEN_PARTY,"NULL","X")

this gives you 2 columns where you can see all the intermediate results...then you can tie it up with a compare of these with something like:

Final: Iif(Tfind = "x", Ofind,Tfind)

I'm not saying my logic is what you specifically want in your case - am just making up an example. But when one has many variations to toggle thru and you get into more columns - it is easier to deal with it in this manner then the nested approach.

Hope this helps.