Quarterly Report - Selection Formula Problems 1

[Bennie47250]

Using Crystal version 7.0
MS Access database

I have attempted to get this selection formula to work for several hours without success and before I completely give up on it, thought I would ask for help.

As the formula is right now, if I run it and enter 2 for the 2nd Quarter, the only records returned are for the product number M140024.

If I remove the code for the 3 and 4th quarter, it will then pick up the records for the product like "MCHW*"

Apparently the code is never getting to the OR part of the formula.

How can I change this formula so it will get both products?

Thanks
Bennie

*******My Formula*********

if {?Quarter} = 1 then

{MyTable.LorP} = "P" and
{MyTable.Month Key} in [3, 2, 1] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} = "M140024"

else if

{?Quarter} = 2 then

{MyTable.LorP} = "P" and
{MyTable.Month Key} in [4, 5, 6] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} = "M140024"

else if

{?Quarter} = 3 then

{MyTable.LorP} = "P" and
{MyTable.Month Key} in [7, 8, 9] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} = "M140024"

else if

{?Quarter} = 4 then

{MyTable.LorP} = "P" and
{MyTable.Month Key} in [10, 11, 12] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} = "M140024"

OR

if {?Quarter} = 1 then

{MyTable.LorP} = "L" and
{MyTable.Month Key} in [3, 2, 1] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} like "MCHW*"

else if

{?Quarter} = 2 then

{MyTable.LorP} = "L" and
{MyTable.Month Key} in [4, 5, 6] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} like "MCHW*"

else if

{?Quarter} = 3 then

{MyTable.LorP} = "L" and
{MyTable.Month Key} in [7, 8, 9] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} like "MCHW*"

else if

{?Quarter} = 4 then

{MyTable.LorP} = "L" and
{MyTable.Month Key} in [10, 11, 12] and
{MyTable.Period Key} = {?Year} and
{MyTable.Product_#} like "MCHW*"

********End**********

 

[IanWaterman]

Try

if {?Quarter} = 1
then
{MyTable.Month Key} in [3, 2, 1] and
{MyTable.Period Key} = {?Year} and
(
({MyTable.LorP} = "P" and {MyTable.Product_#} = "M140024")
or ({MyTable.LorP} = "L" and {MyTable.Product_#} like "MCHW*")
)
else
if {?Quarter} = 2 then
{MyTable.Month Key} in [4, 5, 6] and
{MyTable.Period Key} = {?Year} and
(
({MyTable.LorP} = "P" and {MyTable.Product_#} = "M140024")
or ({MyTable.LorP} = "L" and {MyTable.Product_#} like "MCHW*")
)
else
if {?Quarter} = 3 then
{MyTable.Month Key} in [7, 8, 9] and
{MyTable.Period Key} = {?Year} and
(
({MyTable.LorP} = "P" and {MyTable.Product_#} = "M140024")
or ({MyTable.LorP} = "L" and {MyTable.Product_#} like "MCHW*")
)
else
if {?Quarter} = 4 then
{MyTable.Month Key} in [10, 11, 12] and
{MyTable.Period Key} = {?Year} and
(
({MyTable.LorP} = "P" and
{MyTable.Product_#} = "M140024")
or ({MyTable.LorP} = "L" and {MyTable.Product_#} like "MCHW*")
)

Ian

 

[Bennie47250]

Ian,

This works, THANKS.

So that I can learn something here, could you tell me why my formula was failing and how you corrected it?

Thanks
Bennie

 

[IanWaterman]

If then else execute the first true condition and then ignore the rest, thus it never made it to your OR

Ian

 

[Bennie47250]

Why are there double parentheses around
(({MyTable.LorP} = "P" and {MyTable.Product_#} = "M140024")
or ({MyTable.LorP} = "L" and {MyTable.Product_#} like "MCHW*"))