Dealing with Null Values in a Format Expression

[sbf2014]

Hi Everyone!

I am trying to use the expression below to transpose strings of 4-6 numbers (140504) to a string of 8 numbers with "20" in front (20140504). Some of the [Receive_Date] fields are empty or are 0, and in those cases, I need the expression to return a null value.

New_Receive_Date: IIf([Receive_Date]=0,"",IIf([Receive_Date]="","",Format([Receive_Date_YMD],"000000")+20000000))

The Format(...) part of the expression works fine on its own, but I am new to dealing with null values in my expressions, and when I add the rest of the expression, all '#Error' values are returned.

Can anyone look at what I have above and see where I am going wrong?

Thank you!

 

[SmallTomato]

Test this:

IIf([Receive_Date] Is Not Null and [Receive_Date]<>0,Format([Receive_Date_YMD],"20000000"),Null)

Or try

IIf([Receive_Date]<>"" and [Receive_Date]<>0,Format([Receive_Date_YMD],"20000000"),Null)

 

[sbf2014]

Thank you SO much! That worked perfectly and I was able to apply the concept to another two expressions. =)