Simple formula problem

[DBAFrog]

Oh the inhumanity of it...LOL

I am getting an error that states "A summary / Running total field could not be created" when I put this in a formula. I want to drop about 40 of these calculations in the Report Footer. ( This is pie in Access and is driving me up a wall in CR )

Formula 1:
Count({Query.CHARGE_CODE} like "182702&quot

Formula 2:
Count({Query.CHARGE_CODE} like "18270s" and
{Query.CHARGE_CODE} <> &quot;182702A5&quot; )

And so on thru all my calcs.
I really don't want to set up 40 groups if I can avoid it.

Any suggestions appreciated.

TIA..DBAFrog

 

[synapsevampire]

The second half of this:

Count({Query.CHARGE_CODE} like &quot;18270s&quot; and
{Query.CHARGE_CODE} <> &quot;182702A5&quot; )

is redundant anyway, and it doesn't work this way.

The like pattern works as follows:

? = wildcard for a single character
* = wildcard for any group of characters

You might want to consider using Running Totals with something like the following in the evaluate use a formula for the more complex count:

{Query.CHARGE_CODE} like &quot;18270*&quot; and
{Query.CHARGE_CODE} <> &quot;182702A5&quot;

For the simpler one:

{Query.CHARGE_CODE} like &quot;182702*&quot;

Also, you can just group by

{Query.CHARGE_CODE}

and right click a field in the details section and select insert use a formula->count

This will give you counts for all groups.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Naith]

I see where you're going with that syntax, but that 'like' business doesn't fly with 'count' - or with any other summary function. That's not unique to Crystal. Most real rdbms databases are like that.

What you want to do, DBAFrog, is create a RunningTotal to count your fields. Use a formula on the 'Evaluate' section to do:

Left(ToText({Query.CHARGE_CODE},0),5) = &quot;18270&quot;

Alternatively, you could use formulas and variables:

WhilePrintingRecords;
NumberVar Count1;

If Left(ToText({Query.CHARGE_CODE},0),5) = &quot;18270&quot;
Then Count1 := Count1 + 1
Else Count1;

etc.

Good luck,

Naith

 

[DBAFrog]

Thanks to both of you for the advice.

I was focusing on the syntax for evaluating with the like operator.

P.S. I couldn't find anything on the 'ToText' operator. Do you have a link for more info? The Criminal Codes are alpha\numeric and I have to eval fields like this ALL the time.

 

[Naith]

I was only putting a ToText in there in case your field was numeric. But, it doesn't seem that it is now that I look at &quot;182702A5&quot;. (If you ever want a little more info on any of the functions you see mentioned here at Tek-Tips, check out F1 on your application, and type the function in. The Crystal Reports help files are as good as any I've had to use before.)

You can still use the 'like' operator.

Left(ToText({Query.CHARGE_CODE},0),5) = "18270"

would become

{Query.CHARGE_CODE} like "18270*"

All the best

Naith

 

[Ngolem]

DBAFrog -

You don't really have to use summary functions to evaluate a count if you seem to be having troubles doing it that way.
I like Naith's latter approach...it is simpler.

Simply set an initialization formula in a Group Header or report header if it is only run once.

@Initialization (if in a Group header)(suppressed)

WhilePrintingRecords;

// prevents accidental resets if group header resets
if not inrepeatedGroupHeader then
(
numberVar Count1 := 0;
numberVar count2 := 0;
);

Now place this next formula in the details(I assume) suppressed


@CalcCount
WhilePrintingRecords;

numberVar Count1;
numberVar count2;

If Left({Query.CHARGE_CODE},5) = &quot;18270&quot; Then
Count1 := Count1 + 1;

If Left({Query.CHARGE_CODE},5) = &quot;18270&quot; and
{Query.CHARGE_CODE} <> &quot;182702A5&quot; Then
Count2 := Count1 + 2;

Using this you can access the count of each variable any time you want in other formulas.

Hope this helps


Jim Broadbent