How can I get a+b=c??

[parrotheadman]

I have two summary fields(the results of formulas) named @InProcess and @Committed. They are showing up perfectly on my report, side by side, just like I need them to. Now, my mgr wants me to add an additional column that adds both of these fields(I guess he can't count, but that's another story). Anyway, I try to add both formula fields
{@InProcess} + {@Committed}, but it doesn't give me any valid results.

So, then I completely write out both formulas, using an "or" statement whereas if they are true, return a 1, if not true, a zero, but no luck. Any ideas?

 

[Naith]

If InProcess or Committed use an evaluation time function, like WhilePrintingRecords, remember to include this in your summing field.

Otherwise, paste both your formulas, and illustrate what you mean when you say you're getting invalid results.

Naith

 

[parrotheadman]

if ((({vLOIS1.ComKeyDate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("d",-1,Currentdate))) or ({vLOIS1.DispCode}=0)) then 1 else 0

 

[Naith]

And the other formula?

Remember to include an example of the results that you say you're getting.

 

[parrotheadman]

@commit = (({vLOIS1.ComKeyDate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("d",-1,Currentdate))

@inprocess = {vLOIS1.DispCode}=0

As for results, my running summary (a+b=c) is coming out as
268+99=109 --which is incorrect.

 

[Naith]

That doesn't make sense.

Both of those formulas are boolean, returning either True or False. I don't see how you get 268 or 99 from just these two fields.

Naith

 

[parrotheadman]

I use them in conjunction with an if...then statement. If they are true then 1, else 0

 

[Naith]

Ah, I see.

And you say you've already tried the following and get the same results:

If (({vLOIS1.ComKeyDate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd("d",-1,Currentdate))
and {vLOIS1.DispCode}=0 then 2
else (if (({vLOIS1.ComKeyDate} in Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)) and {vLOIS1.DispCode}<>0)
or
(if (({vLOIS1.ComKeyDate} <> Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)) and {vLOIS1.DispCode}=0) then 1
else 0

Try starting both @InProcess, @Committed, and your adding formula with WhilePrintingRecords.

Naith

 

[Naith]

There shouldn't be a 2nd If in the OR statement:

...or
((({vLOIS1.ComKeyDate} <> Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)) and {vLOIS1.DispCode}=0) then 1
else 0

Apologies for any confusion.

Naith

 

[parrotheadman]

It's still telling me I'm missing an )

Any idea where?

 

[Naith]

Let's get rid of the excess parentheses and then we can see what we're doing:


If {vLOIS1.ComKeyDate} in
Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)
and {vLOIS1.DispCode}=0 then 2
else (if ({vLOIS1.ComKeyDate} in
Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)
and {vLOIS1.DispCode}<>0)
or
{vLOIS1.ComKeyDate} <> Date(Year(CurrentDate),Month(CurrentDate),1) to Dateadd(&quot;d&quot;,-1,Currentdate)
and {vLOIS1.DispCode}=0 then 1
else 0)

This formula is side-stepping the issue. Really, you might want to get to the bottom of the initial factor of adding one formula to the other. But, if you're pressed for time, the above formula should be okay.

Naith

 

[parrotheadman]

Sorry, Naith, but it's still not coming out. The numbers get closer, but no cigar. I'll continue working with it for now and see where I get. Thanks for the effort, though.

 

[parrotheadman]

O.K. Naith, I think I found out what the problem is. When I am creating @Inprocess and @commit during my query, if the result is true, I will get the appropriate result of 1, respectively. BUT
if the @commit field is null(because zeros are not showing on this field), then my total count of both fields shows up as a null value also. So, how can I get my @inprocess to reflect a 0 when there is no data?

 

[Naith]

Sounds like you're on the right lead. Start both your formulas off with:

If IsNull({Field}) Then 0
Else
...<your formula here>

Naith

 

[parrotheadman]

Naith,
That's exactly what I did last night and voila! It worked. It seems you need to go through and get through all the &quot;nulls&quot; and &quot;zeros&quot; first then you can move onto the &quot;ones&quot; or 1. Thank you so much for all your help. I'll make sure and notate your assistance.