Using Select Expert "if contains" from parameter 4

[dlongnecker]

I've been tasked with creating a dynamic report that can prompt the user (with a parameter field) for a free form word or phrase then search a single database field for that variable. I've gone through the forum and documentation, but I can't find (or don't know what it's called) the keyword for the task. Below is a layout of what I'm trying to do.

1. Report opens for viewing, user is prompted for two paramenters, the date range and a string field. For the example, let's call the string field "searchvalue".

2. The Select Expert then inspects (tasks.description) for *"searchvalue"*, only displaying those results that meet the criteria.

3. The report is generated and displayed to the user.


Any assistance would be very beneficial.

Thanks!

-David




David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[Reebo99]

Donlt know how your using the date range, but something like this should work :

{table.DateField} in {?DateRangParam} and
task.description like ["*"&{?SearchValue}&"*"]

Sorry, I haven't got CR with me at the moment so I can't fully test.



Reebo
UK

 

[dlongnecker]

Beautiful! Works like a charm! I didn't even think... The *'s are separate strings! Of course!

I really appreciate your help!!

-David

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[rhinok]

I like Reebo's solution (*), because it allows the user to enter a partial string without having to also explicitly place wildcards. The user can simply enter the exact text required and click the <refresh> button.

 

[dlongnecker]

Quick question though... is there a way around the &quot;Blob field or memo field cannot be used in a formula.&quot;? I tried applying it to the primary field I needed (a long description field).

-David

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[synapsevampire]

Create a SQL Expression(s) from this field (max 254 each) using CAST or CONVERT, and then you can use it in the record selection formula.

-k

 

[vidru]

Your SQL Expression should look like this:

convert(varchar(254),table.MemoField)

and your record selection will have to be edited to look like:

{table.datefield} in {?DateRange} and
{%sqlExpr} like &quot;*&quot; + {?SearchField} + &quot;*&quot;

-dave

 

[dlongnecker]

Synapsevampire/vidru --

Great, that worked like a charm... now, from reading this, it's only pulling the first 254 characters of the memo field, right?

-David

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[synapsevampire]

That's correct, but you can use multiple SQL Expresions, and then gang them back together as a single entity by dropping them into a Text Object.

-k

 

[dlongnecker]

{%memo1} & {%memo2} & {%memo3}, for example?

David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259

 

[Reebo99]

Or get a copy of CR9 which has a 64k limitation

Reebo
UK

 

[dlongnecker]

We've got it, but the app I'm writing for can't read CR9 documents and updating the .dll's voids our service contract. LOL.

-David


David R. Longnecker
CCNA, MCSA, Network+, A+
Management Information Services
Wichita Public Schools, USD 259