Calculated Field with Appropriate Operators?

[excellenthelp]

Accessors,



I want to figure out how to formulate a calculated field properly that would look for records that has at least 'annual report' in a specific field.
For example, a record with a field ([DocumentType]) with "2013 annual report", another record with a field ([DocumentType]) with "2012 annual report" and then I would like to count in those records and exclude the other records.

Here is the formula I wrote:

Report: IIf([TABLE]![DocumentType]<>"Annual Report",[TABLE]![Name],"")

Maybe I am using the wrong operator or something....?

[excellenthelp]

maybe I should use a wildcard in the formula? I am not sure how to...

[jfg863]

Well since you want "Annual Report" to show up, the <> symbol means does not equal "Annual Report" so that would make it where it would not show up. I would try just going to the specific column that has those words in it and make a query that you could put in the criteria of: Like * & "Annual Report" & *

[John_G]

Hi -

You need to use the Instr function, which looks for the starting position of one string within another longer one. In your case, you need to look for the string "Annual Report" in the table field [DocumentType], something like this:

IIf(instr([TABLE]![DocumentType], "Annual Report") > 0 ,[TABLE]![Name],"")

If you want to use this to include or exclude records for inclusion in a report, this is not quite the right approach - can you clarify waht you are trying to do?

John