Dates!

[John Richardson]

Why won't my Date filters work now?
I had(have) them set up using field Payback_Due>=date1 (which is of course a variable), etc... (ie+

[Tom Cone Jr]

RE: Dates!

How about posting the specifics?

[martinwcole]

RE: Dates!

given that the variables are dates!
otherwise
variable1=ctod(ui_get_date("Enter beginning date",""))
variable2=ctod(ui_get_date("Enter ending date",""))

date1 is a valid field in the file
query.filter="between(date1,variable1,variable2)"
idx=file1.query_create()
recs=idx.records_get()
if recs

[John Richardson]

RE: Dates!

I'm talking about a filter in a report I have used for some time...
Payback_Due is a date field
Date1 is a variable, as is Date2...
Filter gets records where Payback_Due is greater (or equal) to Date1 and less (or equal) to Date2...
Pretty simple, however...
When I enter and load the variable dates with 01/15/2001 for Date1 and 01/20/2001 for Date2, report runs wild! I have to cancel or escape the "view" or build because it runs into THOSANDS of pages! (I stopped on at 1,900+ pages!)
SHOULD be 4 or 5 max!
Now the thing that driving me NUTTZ! is it works perfectly using 12/1/2000 to 12/31/2000 or any other dates in 2000!
What have I created here a Y2K+1 nightmare?

Any ideas on this seemingly SIMPLE problem would be appreciated.

PS the code has worked for MONTHS in 2000. It's NOT my script!

[Cian Chambliss]

RE: Dates!

What version of alpha five are you using?

also - are you using 4 places to specify year - or two places
(you can use two places if you set the 21st century date
window to be bigger than the dates that will be entered in your
application -- it may be you simply need to make the date window bigger
which will cause 2 digit year wrap around to occur later-
this can be changed in viewsettings).

example of year wrap-around:

if date-window was set to 1:
? {1/1/0}
= {01/01/2000}

? {1/1/1}
= {01/01/1901}

if date window was set to 20

? {1/1/0}
= {01/01/2000}

? {1/1/1}
= {01/01/2001}

? {1/1/19}
= {01/01/2019}

? {1/1/20}
= {01/01/1920}




I tested the alpha five 4.03 build from the interactive window, and
it appears to be handling dates in 2001 without a problem.

dt1 = {01/15/2001}
dt2 = {01/21/2001}
dt = {01/16/2001}
? dt1

[Tom Cone Jr]

RE: Dates!

John, I didn't mean to imply that there's a problem with your script, it's just that there are several different ways the filter expression might have been constructed. By letting us see the actual filter expression you are using we have a better shot at suggesting a specific solution.


-- tom

[John Richardson]

RE: Dates!

It 4.03, and I had the dates set to 20...
(I've been using filters like this since the old DOS version(s) on my old 386, so it's NOT the variables or fields)
I'll try demand for 4 digit years and see if that helps, I guess.
Sumptin wiiieerd goin on here..
I'm using windows98, but THAT shouldn't matter.
What I just don't get is WHAT is it doing, and WHY does it work with 2000 dates?
THANKS all for your feedback...
I'm STUMPED!

[John Richardson]

RE: Dates!

I understand... (I was just trying to NOT clutter up what SHOULD be a straight foreward thing)
HERE'S the actual filter expression:
Write_Off={}.and.Payback_Due>=Var->Date1.and.Payback_DueDate2.or.Int_Pd1.t..and.Int_Date1{}.and.Int_Date1>=Var->Date1.and.Int_Date1Date2.or.Int_Pd2.t..and.Int_Date2{}.and.Int_Date2>=Var->Date1.and.Int_Date2Date2.or.Int_Pd3.t..and.Int_Date3{}.and.Int_Date3>=Var->Date1.and.Int_Date3Date2

[John Richardson]

RE: Dates!

This is better! :-)
Write_Off={}.and.Payback_Due>=Var->Date1.and.Payback_DueDate2.or.Int_Pd1
.t..and.Int_Date1{}.and.Int_Date1>=Var->Date1.and.Int_Date1Date2.or.
Int_Pd2.t..and.Int_Date2{}.and.Int_Date2>=Var->Date1.and.Int_Date2Date2
.or.Int_Pd3.t..and.Int_Date3{}.and.Int_Date3>=Var->Date1.and.Int_Date3Date2

[John Richardson]

RE: Dates!

I'm using 4.03 build 230...
Is THAT what you "tested"?

[Stan Mathews]

RE: Dates!

I'll probably wish I had stayed out of this one but it seems to me that you should be using something like:

Write_Off={}.and.((Payback_Due>=Var->Date1.and.Payback_DueDate2).or.(Int_Pd1
.t..and.Int_Date1{}.and.Int_Date1>=Var->Date1.and.Int_Date1Date2).or.(Int_Pd2.t..and.Int_Date2{}.and.Int_Date2>=Var->Date1.and.Int_Date2Date2)
.or.(Int_Pd3.t..and.Int_Date3{}.and.Int_Date3>=Var->Date1.and.Int_Date3Date2))

This doesn't explain why you are having problems with 2001 dates, but then I don't know what all your dates represent and the data in the fields.

[Tom Cone Jr]

RE: Dates!

John, I was surprised at the absence of parentheses in your expression, and see that Stan had the same reaction by suggesting that it be rewritten slightly to include them. The User's Guide (at pg 216) recommends using parentheses to control the order in which logical operators (.and., .not., .or.) are used in an expression.


As noted, this does not explain why your expression worked during CY2000, and fails now, but I think it's a step in the right direction. My guess is that you have other values in the table (or perhaps values are missing) right at the beginning of the year, and this is causing the expression to evaluate differently without parentheses than it did laast year.


Good luck.


-- tom

[Cian Chambliss]

RE: Dates!

Yes - its build 230, Is there any way I could get my hands
on the application with some sample data so I can see if
I can reproduce the problem on my end?

[csda1]

RE: Dates!

Hi John

I would try something like this;

(remspecial(dtoc(Write_Off))=="".and.Payback_Due>=Var->Date1.and.Payback_DueDate2)
.or.
(Int_Pd1=.f..and.remspecial(dtoc(Int_Date1))=="".and.Int_Date1>=Var->Date1.and.Int_Date1Date2)
.or.
(Int_Pd2=.f..and.remspecial(dtoc(Int_Date2))=="".and.Int_Date2>=Var->Date1.and.Int_Date2Date2)
.or.
(Int_Pd3=.f..and.remspecial(dtoc(Int_Date3))=="".and.Int_Date3>=Var->Date1.and.Int_Date3Date2)


The main differences are;

The test for a blank date is done in a more reliable way(for some reason A5 returns a blank date as { / / } as opposed to {}. This method of checking for a blank date works for all cases.

The parenthesis around the and portions of the equations. Alpha 5 (as opposed to A4) seems to considers And and Or at the same level of evaluation, which means the expression is definitely evaluated wrong without the parenthesis

I compared Int_Pdx=.f. as opposed to Int_Pdx.t. because it is a more straightforward, but would work either way.

Finally, if you get this to work, change the date comparisons to Between statements which are more efficient

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]

[Selwyn Rabins]

RE: Dates!


If you try a "hard coded" expression (i.e. refering to a date constant, rather than a date variable), does it work?

for example:
payback_due >= {01/15/01}

if it does work, then you could try this as your expression:

payback_due >= "{"+dtoc(date1)+"}"


(this is equivalent to an expression with a "hard coded" date. e.g.