Dyanset SortExpr question

[tjcusick]

Okay most of you are probably gonna ignore this message just cus of my sortexpr, which is fine... it gives me a headache just looking at it also.

I'll set this up... We have a program that tracks families and their individuals in the families. Each family profile can have a home address and an alternate address. The alt address has a start and end date (A_EFF_DT and A_END_DT) to tell the program when to use that date for printing mailing labels. (Ex. If today's date is between or equal to the start and end date use the alt address)

Oh and BTW we are using a DBF file structure...

well we have a dynaset that is populated with the selected families and when someone selects to print mailing labels sorted by the Zip code i would like it to determine where each one goes based either on the Home Zip (ZIP) or the Alt Zip (A_ZIP) depending on the start and end dates.

now for the good part... this is what i have as far as my sortexpr... i split it up to make it easier to read...

IIF((NO_ALT_IND="1"), ZIP,
IIF(((VAL(A_EFF_DT)>VAL(DTOS(DATE()))) .or. (A_EFF_DT="        ") .or. ((SUBSTR(A_END_DT,1,4)<>"    ") .and. (VAL(A_END_DT)<VAL(DTOS(DATE()))))),ZIP, 
IIF((VAL(A_END_DT)> VAL(DTOS(DATE()))),A_ZIP, IIF(((VAL(A_EFF_DT)<=VAL(DTOS(DATE()))) .and. (VAL(A_END_DT)>=VAL(DTOS(DATE())))),A_ZIP, 
IIF(((((((VAL(SUBSTR(DTOS(DATE()),1,4))-1)*10000)+VAL(SUBSTR(A_EFF_DT,5,4)))<= VAL(DTOS(DATE()))) .and. (((VAL(SUBSTR(DTOS(DATE()),1,4))*10000)+VAL(SUBSTR(A_END_DT,5,4)))>=VAL(DTOS(DATE())))) .or.
((((VAL(SUBSTR(DTOS(DATE()),1,4))*10000)+VAL(SUBSTR(A_EFF_DT,5,4)))<= VAL(DTOS(DATE()))) .and. ((((VAL(SUBSTR(DTOS(DATE()),1,4))+1)*10000)+VAL(SUBSTR(A_END_DT,5,4)))>=VAL(DTOS(DATE()))))) ,A_ZIP ,ZIP)))))

Yeah Ugly i know but for some reason it was working before and now it's not working and just seems to be sorting on the A_ZIP regardless of the start and end dates...
And yes i double checked all the Parenthesis...

If you wanna closer look i suggest copying the code and pasting it into Notepad++ (

http://notepad-plus.sourceforge.net/uk/site.htm)

a great free replacement for Windows Notepad.

Anyone have any ideas... Or if you can think of a better cleaner way of doing this, i would be most appreciative...

Thanks in advance and sorry for the headache...

Tom Cusick

 

[tjcusick]

Ok just for those techs out there that might even be remotely interested in an answer to my above question...
Following is what i figured out...

IIF((NO_ALT_IND="1"), ZIP, 
(IIF( ((VAL(A_EFF_DT)>VAL(DTOS(DATE()))) .or. (A_EFF_DT="        ") .or. ((SUBSTR(A_END_DT,1,4)<>"    ") .and. (VAL(A_END_DT)<VAL(DTOS(DATE()))))), ZIP, 
(IIF( (VAL(A_END_DT)>VAL(DTOS(DATE()))), A_ZIP, (IIF( ((VAL(A_EFF_DT)<=VAL(DTOS(DATE()))) .and. (VAL(A_END_DT)>=VAL(DTOS(DATE())))), A_ZIP, 
(IIF( (VAL(SUBSTR(A_EFF_DT,5,4)) > VAL(SUBSTR(A_END_DT,5,4))) ,
(IIF( (((((VAL(SUBSTR(DTOS(DATE()),1,4))-1)*10000)+VAL(SUBSTR(A_EFF_DT,5,4))) <= VAL(DTOS(DATE()))) .and. (((VAL(SUBSTR(DTOS(DATE()),1,4))*10000)+VAL(SUBSTR(A_END_DT,5,4))) >= VAL(DTOS(DATE())))), A_ZIP, ZIP)),
(IIF( (((((VAL(SUBSTR(DTOS(DATE()),1,4)))*10000)+VAL(SUBSTR(A_EFF_DT,5,4)))<=VAL(DTOS(DATE()))) .and. (((VAL(SUBSTR(DTOS(DATE()),1,4))*10000)+VAL(SUBSTR(A_END_DT,5,4))) >= VAL(DTOS(DATE())))), A_ZIP, ZIP))
)))))))))

//IF Do not use Alt Addr checked THEN use Home Zip ELSE
//IF From Date > Today or From Date is blank or Full To Date < Today THEN use Home Zip ELSE
//IF To Date > Today THEN use Alt Zip ELSE
//IF From Date <= Today and To Date >= Today THEN use Alt Zip ELSE
//IF From Month and Day > To Month and Day THEN
//   IF Last Year and From Month and Day <= Today and This Year and To Month and Day >= Today THEN use Alt Zip ELSE Use Home Zip
//ELSE  //IF From Month and Day <= To Month and Day THEN
//   IF This Year and From Month and Day <= Today and This Year and To Month and Day >= Today THEN use Alt Zip ELSE Use Home Zip