my record selection formula 1

[ultimatewilliam]

the formula goes like this:

{ImpJobProcess.dateperformed} in {?BegDate} to {?EndDate} and
((if not isnull({?parProcess1}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess1}))<>0) or
(if not isnull({?parProcess2}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess2}))<>0) or
(if not isnull({?parProcess3}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess3}))<>0) or
(if not isnull({?parProcess4}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess4}))<>0) or
(if not isnull({?parProcess5}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess5}))<>0) or
(if not isnull({?parProcess6}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess6}))<>0) or
(if not isnull({?parProcess7}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess7}))<>0) or
(if not isnull({?parProcess8}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess8}))<>0) or
(if not isnull({?parProcess9}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess9}))<>0) or
(if not isnull({?parProcess10}) then InStr(uppercase({ImpJobProcess.process}),uppercase({?parProcess10}))<>0)) and
IsNull ({ImpJobProcess.datecompleted})

i have 10 parameters where in the user must supply at least one value (not necessarily all so others may be empty). its suppose to select the records where the PROCESS field contains the value of a parameter (parProcessN) within date range, but what happens here is that it actually selects all the records. is there anything wrong with the formula? pls help, thanks!

 

[lyanch]

Blank string parameters are not null, they are zero length..

if {parProcessxx} <> &quot;&quot;

Lisa

 

[synapsevampire]

Try something like:

(
if {?parprocess1} <> &quot;&quot; then
{ImpJobProcess.process} like &quot;*&quot;+{?parprocess1}+&quot;*&quot;
)
or
(
if {?parprocess2} <> &quot;&quot; then
{ImpJobProcess.process} like &quot;*&quot;+{?parprocess2}+&quot;*&quot;
)

This will pass the SQL and should net reasonable performance.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[ultimatewilliam]

i'll try them all, thanks.

 

[ultimatewilliam]

it still taking all the records. . .
i think the problem is that, all the parameters must be supplied with a value in this case. if i did that, it will read only the records according to the formula. but if the other parameters is &quot;&quot;, then crystal will take all the records. i believe theres something wrong with the formula so the the user may or may not fill all the parameters. thanks!

 

[Naith]

Should you not be using AND in the place of OR?

If you can support the use of OR, simplify your formula in a post by illustrating a couple of parameters only, and state what you expect it to achieve with an example of data. As it is, your formula is pretty confusing, and it's not clear to me what you're trying to do.

Naith

 

[scottostanek]

Since you only need to know if at least one parameter is filled, why not concatenate them together and test length
of the result?

IE

if (Length({?parProcess1}&{?parProcess2}&{?parProcess3}&
{?parProcess4}&{?parProcess5}&{?parProcess6}&
{?parProcess7}&{?parProcess8}&{?parProcess9}&
{?parProcess10} ) > 0) and (othertest...)

Scotto the Unwise

 

[synapsevampire]

Yeah, it should be AND, Naith.

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[BoboTekTips]

Hi,

I re-arranged the logic order a bit for easier reading but your original goal was maintained. Basically, based on YOUR original formula, you wanted all PROCESSes started (dateperformed) within the date range (?BegDate and ?EndDate) but incomplete (datecompleted is null) AND containing any (the OR's) of the possible entered value(s) (up to the 10). If your original formula's logic was wrong then my interpretation of it would also be wrong.

Always make your null test the first line of code. Crystal (and also others) will choke on null data and will never get to the rest of the code.

Another alternative is under File, Options, make sure the &quot;Convert Null Field Value to Default&quot; option is checked then you can and would need to simplify the formula below. Try it without checking the convert null option first, then with. Let me know if this works for you.

{ImpJobProcess.dateperformed} in {?BegDate} to {?EndDate}
and isnull({ImpJobProcess.datecompleted})
and
(
({ImpJobProcess.process} like &quot;*&quot; + iif(isnull({?parProcess1}), &quot;&quot;, {?parProcess1}) + &quot;*&quot or
({ImpJobProcess.process} like &quot;*&quot; + iif(isnull({?parProcess2}), &quot;&quot;, {?parProcess2}) + &quot;*&quot or
.
.
.
({ImpJobProcess.process} like &quot;*&quot; + iif(isnull({?parProcess10}), &quot;&quot;, {?parProcess10}) + &quot;*&quot or
)

 

[ultimatewilliam]

bobotektips' interpretation is correct, but your formula is still taking all records - except within date range and datecompleted is null. i also tried the OPTION thing but it didn't work. i think this situation really needs a value for all its parametes. ex: if i put a valid value on the first 3 parameters and an invalid values for the rest (at least they're not empty) then i will have my records. i guess that would be the best solution to this problem.

thank you all!