Hello again helpful friends.
I am having some difficulty displaying some numbers in a report form. My fields are Net.1 Net.2 Net.3 and Net.4. A family could possibly have four children. The Net.1 field will always have an amount. The remaining 3 fields might not have any data and rather then display a zero I would want it to be blank. I must be doing something wrong in my formula. And, to throw in another wrinkle, I want to use a pattern to display a comma and period.
Any help would be appreciated. This was a very complicated database for our day school that I created in Pan 6 many years ago and I want to convert to Pan X. I don’t want to lose any more sleep over this.
Pattern( returns a string. Zeroblank( requires a number.
This would give you an empty string for «Net.2» = 0:
?(«Net.2»=0,"",pattern(«Net.2»,"#,.##"))Thank you KJM,
I was afraid I was not going to get a response so I was playing around with it this morning. This is what I came up with for the formula and it seems to work. I think it is pretty close to yours. I think mine is a little different, I am saying if the field is greater than 1 (meaning it has data) then run the pattern, if not then “” (which will give a blank field). I used carriage returns for the 4 lines of data. Thanks again for your help.
?(«Net.1»>1,pattern(«Net.1»,"#.,##"),"")+¶+?(«Net.2»>1,pattern(«Net.2»,"#.,##"),"")+¶+?(«Net.3»>1,pattern(«Net.3»,"#.,##"),"")+¶+?(«Net.4»>1,pattern(«Net.4»,"#.,##"),"")
Your formula would return an empty space, when the fields are containing the value 1. I think that is not what you want. Replace the “>1” with “>0”, and it should work perfectly.