comments select statement

[jobillborf]

Hi, using version 9. I would like to view all records that have the word full in upper and/or lower case in a comments field. The word full can be anywhere in the comments.

Thank You

 

[synapsevampire]

In the Report->Selection Formulas->Record place something like:

instr(uppercase({table.field}),"FULL")) > 0

Depending upon your database (basic technical information which should be included with every post), you might not get the SQL passing to the database, so creating a SQL Expression which converts the field to all uppercase for conditional checking will resolve this. You can still keep the original field for display purposes.

-k

 

[jobillborf]

K- will this work for the lower case full.

 

[jobillborf]

K- the database is oracle

 

[synapsevampire]

Please test it.

It converts the field to UPPERCASE and then looks for the word FULL in uppercase, so anything in lowercase is already converted to uppercase.

-k

 

[jobillborf]

Thank you k- it works. You really know your stuff. Thanks Again

 

[synapsevampire]

You might prefer to create a parameter to enter the text to search for dynamically, then the record selection would hold:

instr(uppercase({table.field}),uppercase({?MyParameter}))) > 0

Then the report will prompt for the text to search for and only return those rows.

Note that the above will automatically convert the text entered into the parameter to uppercase.

-k