Formula Question

[mmt4331]

Got a question, everyone. I'm trying to count how many times a 12 character 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

 

[synapsevampire]

length(trim({myfield})) = 12

gets you those that have a length of 12.

You can place this in the record selection criteria to limit the report to just those rows, or you can use this as the test condition within a formula.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

Well...a few questions first before I leap in:

1. You show a 12 character numeric string in your
example....are all your strings numeric?
2. is the field you want to test always have 12 characters?
3. Are you looking for complete 12 string matching fields?
ie, would the string "a/020602140000" be a valid
answer because it contains the string you want?

otherwise this is pretty straightforward

just put the formula like this in your record selection formula:

{table.fieldvalue} = {?testString}


Somehow I think it is more complicated....give us ALL the details and we shall help you Jim Broadbent

 

[mmt4331]

That did work and I thank you for it. Now I have to contend w/ the null fields. How do I not have the null fields displayed??

Mark

 

[dgillz]

MMT-

Did you place the formula:
length(trim({myfield})) = 12

In your record selection formula? This should exclude anything that is not a 12 character field from the report.

If you created a formula field by insert, field object, formula field, you did not do the right thing to restrict the records on your report. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[mmt4331]

dgillz:


I put the 'length(trim({myfield})) = 12' formula in and it ommitted everything except for the blanks in my report. I only have one field in my report so I don't know what's going on.

Mark

 

[dgillz]

Ok, is your field a string field or a number field? To test this, click on insert, field object, database field and make sure the icon in the upper right is selected. This will display the field type and length to teh immeidate right of the fields in the field explorer. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[synapsevampire]

dgillz: If it were a number, the test formula would fail.

mark: Where did you place the formula? If this field has a length of 12, then the formula will limit the rows to those with 12 if you placed it in the record selection criteria (use the Report->Edit Record Selection->formula.

You may have other criteria which alters this, post what you have in the record selection criteria, and the SQL CR is generating.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Ngolem]

"That did work and I thank you for it. Now I have to contend w/ the null fields. How do I not have the null fields displayed??"

ok since the formula : length(trim({myfield})) = 12
works except in eliminating nulls use this

not isnull(myfield) and length(trim({myfield})) = 12

Jim Broadbent