String Formula 1

[mmt4331]

Got a question, everyone. I'm trying to count how many times a 12 character (string) field (for ex. 020602140000) occurrs during a certain time period. Only problem is, this field also allows characters such as "N/A", "NONE" and also a space. How would I write a formula that picks out and counts only the 12 character fields and leave out the rest of the junk? Thx.

Mark

 

[Jacque]

You could use the Length function. I've used the following for someting similar although you could always use a running total as well.

numberVar vLen := Length ({field});
numberVar vCount := 0;

if vLen = 12 then vCount := vCount + 1;
vCount;

With the if statement being specific to a length of 12 chars, you don't have to be concerned with "N/A" or "NONE" or a space.

 

[synapsevampire]

Or just use a running total and place the equivalent of jacque's formula in the Evaluate->Use a Formula section:

length(trim({MyTable.MySTringField})) = 12

Note the addition of the trim function.

If you want to limit the fields in the detail to only these 12 char fields, place the following in the suppress formula on the detail line:

length(trim({MyTable.MySTringField})) <> 12

If you're doing both, you should just place something like this in the record selection criteria:

length(trim({MyTable.MySTringField})) = 12

You'll only get those records back where the field is length of 12 and you can just display and count everything

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mmt4331]

Now I'm only getting 1.00 per record. I need a total count of the 12 digit field. Thank you.

 

[synapsevampire]

Place the running total at the end of the group, or if you don't have a grouping, place it in the report footer and make sure that the running total isn't reset.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

ok...so you don't have just a string of 12 characters....you have a string that you are looking for that may be present multiple times within a given string...Is that your problem???

You haven't described your problem very well.

If it is a spectific string that you are looking for then you are going to have to specify it....It doesn't really have to be 12 characters long. This would be done in a Parameter

Eg.

{?SearchString}
Type: String
Description: Enter the the string you want searched.

Now create an initialization formula

@initialization (placed suppressed in the report header)

WhilePrintingRecords;
numberVar TheCount := 0;

In the detail section Suppressed....place this formula

@SearchForString

WhilePrintingRecords;
NumberVar TheCount;
NumberVar PStart := 1;
Numbervar PEnd;
StringVar TempString := {Table.stringtobetested};

PEnd := instr(TempString,{?SearchString},PStart);

while PEnd <> 0 do
(
TheCount := TheCount + 1;
If PEnd <> Length(TempString) then
PStart := PEnd + 1
else
PStart := PEnd;
PEnd := instr(TempString,{?SearchString},PStart);
);


Then display the count wherever

@DisplayCount

WhilePrintingRecords;
NumberVar TheCount;

&quot;The Count = &quot; + ToText(TheCount,0);

perhaps this isn't what you want but...maybe it is ....as I said Your problem is not well defined and just seaching for 12 characters of any stripe seems meaninless to me...Hope this helps








Jim Broadbent