Select Record Problem 1

[Lucieann]

I am trying to select specific records from an Access database using an ODBC connection. Here is the formula I am using:

{@ComingDue} = 1 and
if {?Officer} = "All Officers" then {Exception_Table.Officer} = {Exception_Table.Officer} else
{Exception_Table.Officer} = {?Officer} and
{Exception_Table.Category} in [21, 24, 27, 30, 33, 36, 39, 42, 45, 60, 63, 66, 75, 99]

@ComingDue checks a dateDiff and returns a value of 1 or 0.
{Exception_Table.Officer} is a 3 character alpha value in the Access table. An All Officers option was added to the selection values in Crystal.
{Exception_Table.Category} is a numeric value from 1 to 3 digits in length.

My problem is that if you select the All Officers option, it returns categories other than what are listed above. I get categories like 480, 410, 421, 566, 384. If I select a other than All Officers from the officer list, I do not have this problem.

I use this same formula with another report and have no problems. The only difference is the {Exception_Table.Category} values are all three digits. Could I be having this problem because all the categories I'm selecting are only two digits?

 

[bdreed35]

Try structuring it like this:

{@ComingDue} = 1 and
{Exception_Table.Category} in [21, 24, 27, 30, 33, 36, 39, 42, 45, 60, 63, 66, 75, 99] and
(
if {?Officer} <> "All Officers" then 
    {Exception_Table.Officer} = {?Officer}
else if {?Officer} = "All Officers" then 
    True
)

I think that because of where you put the Category criteria, it was only including it when you didn't pick "All Officers"

~Brian

 

[Lucieann]

That works like a charm!!

I had tried changing the order of my formula, but when I made changes and saved it, it would revert back to the way it was before. It's amazing what those () will do you you!

Thanks so much for all your help!!