Select Statement Not Working as I think it should 1

[Bennie47250]

Using Crystal 7.0

I’m banging my head on the desk trying to figure this out but not having any luck. Can anyone determine why this is not working? (the formula, not the head banging…..)

This formula is not working

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{ Mytable.Country } = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) and
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 ELSE
{MYTABLE.ENTRYDATE} = CurrentDate

or

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
Mytable.Country = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 else
{MYTABLE.ENTRYDATE} = CurrentDate

However if I take the day of week checking out it does work

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
Mytable.Country = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) and
{MYTABLE.ENTRYDATE} = CurrentDate
or

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
Mytable.Country = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
{MYTABLE.ENTRYDATE} = CurrentDate

 

[lbass]

I'm not seeing any difference between the clauses on each side of the "or"--please explain.

-LB

 

[Bennie47250]

In my attempt to make the table and field names genetic, I messed up on of the table/field names.
Sorry for the confusion

Here is a corrected formula.

The top formula checks the order records for the country
The bottom formula check the customer master records for the country

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{MYTABLE.COUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) and
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 ELSE
{MYTABLE.ENTRYDATE} = CurrentDate
or

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{CUSTTABLE.HOMECOUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 else
{MYTABLE.ENTRYDATE} = CurrentDate

If I take the day of week checking out it does work

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{MYTABLE.COUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) and
{MYTABLE.ENTRYDATE} = CurrentDate
or

{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{CUSTTABLE.HOMECOUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
{MYTABLE.ENTRYDATE} = CurrentDate

 

[lbass]

Add parens as follows so the interpretation of the if/then is correct:

(
(
{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{MYTABLE.COUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) and
(
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 ELSE
{MYTABLE.ENTRYDATE} = CurrentDate
)
)
or
(
{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{CUSTTABLE.HOMECOUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
(
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 else
{MYTABLE.ENTRYDATE} = CurrentDate
)
)
)

-LB

 

[Bennie47250]

That did it, Thanks LB

Now if I understood what you did here, it would help me write my formuals better......

 

[lbass]

The parens just indicate what clauses 'go' with each other. Without them, you could incorrectly interpret the if/then to mean, e.g.,

if DayOfWeek (CurrentDate) = 2 then
{MYTABLE.ENTRYDATE} >= CurrentDate -2 ELSE//all of the rest
(
{MYTABLE.ENTRYDATE} = CurrentDate
or
{MYTABLE.ORDERTYPE} like ["EXR", "H*", "L*", "NL*"] and
{CUSTTABLE.HOMECOUNTRY} = "MEXICO" and
not ({MYTABLE.LINETYPE} in ["C", "T"]) AND
if DayOfWeek (CurrentDate) = 2 then {MYTABLE.ENTRYDATE} >= CurrentDate -2 else
{MYTABLE.ENTRYDATE} = CurrentDate
)

And the other situation is how the "or" should be interpreted.

-LB

 

[Bennie47250]

Thanks for taking the time to teach.