combining filters

[parkfairfax]

Hello,

I am trying to combine the following 2 statements and keep getting an error when I try to set to filter using the following. (By themselves, each statement works, but together I get no items returned in browse mode). Assume that table query1 is already open.

set filter to upper(substr(str(year(currdate),4,0),1,4)) = "2002" and upper(substr(cmonth(currdate),1,3)) = "Oct"

Can you see anything wrong with this statment?

Thank you.

 

[Mike Gagnon]

upper(substr(cmonth(currdate),1,3)) = "Oct"

Hard to say but shouldn't the above statement read:

upper(substr(cmonth(currdate),1,3)) = "OCT"





Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[parkfairfax]

yes, your right, that was a typo. The problem still stands. Thanks.

 

[wgcs]

How about:

set filter to year(currdate)=2002 and month(currdate)=10

 

[wgcs]

BTW, are you SURE you have at least one record that has currdate in October 2002?

 

[parkfairfax]

I took your suggestion and tried...
set filter to upper(substr(str(year(currdate),4,0),1,4)) = "2002" and;
month(currdate)=10

Again, the same problem exists..your statment..set filter to month(currdate) = 10 works fine, and my statement by itself runs fine, but the two together..no luck.

 

[Mike Gagnon]

I took your suggestion and tried...
set filter to upper(substr(str(year(currdate),4,0),1,4)) = "2002" and;
month(currdate)=10

I believe you cannot filter on the same field twice.
Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[parkfairfax]

The program that we have incorporates this type of statement, but you could be right. I will have to test and see.

 

[Mike Gagnon]

May I suggest:

Local dDate
STORE "2002/10" to dDate
set filter to SUBSTR(currdate,1,7)= dDate

You don't really need UPPER since you are using numeric characters. Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[wgcs]

Have you checked to be SURE that you DO have records whose CurrDate is in Oct 2002?

Try:

COUNT TO nCnt FOR year(currdate)=2002 and month(currdate)=10
WAIT WINDOW TRAN(nCnt)

to see how many you have.

(Also, USE the table exclusively and REINDEX to make sure we're not just having a corrupted index issue)