I am Using Crystal Report 11 Prof.

 

The DB is MS ACCESS

 

Here is what i trying to do:

 

I have a Table in my DB Name "Risk"

The Column Name "Status"

The Column Data Can Only have three data type: "Open", "Proposed" & "Closed"

 

On The Column "Status": I have 8 "Open" and 1 "Proposed". 

So a Total of 9.

 

I need a formula that will:

 

Count the number of occurrences of a text string (i.e "Open") in a column:

 

On This Column "Status": I have 8 "Open" and 1 "Proposed". 

 

This is what i think the formula should look like:

 

Sum ({Risk.Status})-LEN(SUBSTITUTE('Status',"Open",""))/LEN("Open")

 

This formula is not working is telling me that i need more ")".  I can do it on excel but i am trying to create the formula in the "Formula Field" and Called It "Counting Open Risk"

 

This is the Formula that i used to count the number of Risk in the Project:

 

DistinctCount ({Risk.ID}) [This returns 9] *Which is correct....

 

Well, I hope someone here can help me with this small issue i am having...

 

thank you for help in advance.

 

JoseR