Is Not Null

[shijuantony2002]

I need to put the following in the query expression column, can you advise, what would be wrong here. Am not good in building expression, so please correct it.

Havent read in many post the Is Not Null function, so please tell if there are any alternative.

Remit Bank Name : IIf(Is Not Null([Conv_Curr_ if_other_than_base]),dlookup(“[Currency]”, “[Remit to Bank information]”,”[Currency] = “ & “ [Conv_Curr_ if_other_than_base]”,IIf (Is Not Null ([Base_Curr]), dlookup(“[Currency]”, “[Remit to Bank information]”,”[Currency] = “ & “[Base_Curr]), [Bank Name],""))


I need the expression to identify, if [Conv_Curr_ if_other_than_base] is not null then lookup the field situated in a table [Remit to Bank Information] under field [Currency] and if this field is equal to the field [Conv_Curr_ if_other_than_base] then pull the data from field [Bank Name] under table [Remit to Bank Information].

Had also attached the db for reference.

 

[JohnLee]

Hi,

If I understand what you are asking, If the Conv_Curr_ if_other_than_base field value [from the MT5BY - offbook Buy table] is greater than nothing, then populate it with the value of the field Currency from the "Remit to Bank Infomation" table. and if the Conv_Curr_ if_other_than_base field does equal the Currency field value, get the data from the field Bank Name from the "Remit to Bank Information" table, but you don't say where you want that data to go?

This will get you the first part of your question:


"SELECT [MT5BY - Offbook Buy].[Fund Number], [MT5BY - Offbook Buy].[Buy Amount], IIf([Conv_Curr_ if_other_than_base] Is Not Null,[Conv_Curr_ if_other_than_base],[Currency]) AS ConvCurrBass, [MT5BY - Offbook Buy].Base_Curr, [MT5BY - Offbook Buy].[Value Date]
FROM [MT5BY - Offbook Buy] INNER JOIN [Remit to Bank information] ON [MT5BY - Offbook Buy].Base_Curr = [Remit to Bank information].Currency;"

John

 

[DCrake]

Remit Bank Name : IIf(Is Not Null([Conv_Curr_ if_other_than_base]),dlookup(“[Currency]”, “[Remit to Bank information]”,”[Currency] = “ & “ [Conv_Curr_ if_other_than_base]”,IIf (Is Not Null ([Base_Curr]), dlookup(“[Currency]”, “[Remit to Bank information]”,”[Currency] = “ & “[Base_Curr]), [Bank Name],""))

What you want to do is to place the Is Not Null on the other side of the statement

IIF([Conv_Curr_if_other_than_base] Is Not Null,True Statement,False Statement)

 

[gemma-the-husky]

bankname=""
if nz(useadifferentcurrency,0)<>0 then
if dlookup("currency","remittable",???)=useadifferentcurrency then
bankname = dlookup("bankname","remittable",???)
end if
end if


this sort of thing?

there was nothing in your notes to indicate how to select the currency - the bits in red above

other than that, change the field names and this should work ok

 

[Brianwarnock]

What you want to do is to place the Is Not Null on the other side of the statement

IIF([Conv_Curr_if_other_than_base] Is Not Null,True Statement,False Statement)

or use Not IsNull

IIF(Not IsNull[Conv_Curr_if_other_than_base],True Statement,False Statement)

Brian

 

[shijuantony2002]

Thanks for all your suggestions