Custom Filter for multiple fields

[jboschee]

Hi everyone,

I'm trying to create a filter that removes all zero's across 3 fields if all 3 fields are zero.

Here is the formula I currently have in the filter.  It works, but it will also take 1,000.00 as well as 0.00.  I need it to be only 0.00.

 [X] AND [Y] AND [Z] != "0.00"

Thanks in advance!

[MarqueeCrew]

How about ...

[x] = 0
AND
[z] = 0
AND
[y] = 0

Cheers,

Mark

[jboschee]

Data is V_String so I get an Invalid type in operator error.

[MarqueeCrew]

ToNumber([x]) = 0 AND

ToNumber([y]) = 0 AND

ToNumber([z]) = 0

[jboschee]

That worked but resulted in a few conversion errors to TONUMBER x,xxx,xxx stopped converting at a comma.  It might be invalid.

[Nate1 · Accepted solution]

The "AND" isn't working as you think in the original formula. The [X] AND [Y] isn't doing anything. You need to add the condition to this statements to make them actually filter.  [X] != "0.00" AND [Y] != "0.00" AND [Z] != "0.00" should work.

Or to expand on the work Mark did you need to replace the commas. 

ToNumber(replace(X],",","")) = 0 AND

ToNumber(replace([Y],",","")) = 0 AND

ToNumber(replace([Z],",","")) = 0

[jboschee]

Thanks Nate!

ToNumber(replace(X],",","")) = 0 AND

ToNumber(replace([Y],",","")) = 0 AND

ToNumber(replace([Z],",","")) = 0

Is what I ended up using.