If then else + Or statements in selection criteria... 1

[robmason10]

I am trying to create selection critiea where the matter number can = parameter input (if parameter = 0 then all are selected) OR (and this I think is the key) department = dept parameter (though again if ALL is entered I was it to select all entries. The syntax I have so far is below but doesn't seem to work (it ALWAYS asks for a matter number so I need to make it non mandatory?)

Any help appreciated!

if {?Matter number} = 0 then true else {tblMatter.ubintMatterID} = {?Matter number} or
If {tblEntityDepartment.strName} = "All" then true else {tblEntityDepartment.strName} = {?Department}

 

[Madawc]

I'd try using brackets, I've found that complex tests often give trouble even when they ought to work. Or break down into two formula fields and test them in Select.

Madawc Williams
East Anglia, Great Britain

 

[robmason10]

Individually the statements work - getting them to work together using the 'or' statement seems to be the issue

 

[synapsevampire]

If a parameter is referenced in the record selection, it will be prompted for.

Set a default value of All for each, and then the user can skip them.

Now your formula might read:

(
if {?Matter number} <> 0 then
{tblMatter.ubintMatterID} = {?Matter number}
else if {?Matter number} = 0 then
true
)
and
(
If {tblEntityDepartment.strName} <> &quot;All&quot; then {tblEntityDepartment.strName} = {?Department}
else if {tblEntityDepartment.strName} = &quot;All&quot; then
true
)

I know that it looks odd, but this format assures that the SQL is passed, and if either parameter (or both) have criteria other than All, it will use it.

-k

 

[synapsevampire]

Sorry, I should have said set the default values to 0 and All

-k

 

[smitty206]

I have had to do parameter work with a lot of reports where the client selects a parameter that is used in the selection of records.

Try this;

(if {?Matter number} = 0 then {?Matter number} in ['0' to 'Z'] else {tblMatter.ubintMatterID} = {?Matter number} and
(If {tblEntityDepartment.strName} = &quot;All&quot; then left ({tblEntityDepartment.strName},1) in ['0' to 'Z'] else {tblEntityDepartment.strName} = {?Department})

This will allow to to select down on both parameter fields. I use the ['0'(Zero) to 'Z'] so that everything is selected and I do not leave it to Crystal to make the everything selection.

When you set your selection criteria to 'true' this really does not tell Crystal what to select.

I hope this helps!!

Smitty

 

[robmason10]

Top work fella - barring a slight error ...

If {?department} <> &quot;All&quot;
instead of
If {tblEntityDepartment.strName} <> &quot;All&quot;

worked a treat!

Thanks a bunch!