SELECT ALLTRIM(c.lastname) + ', ' + ALLTRIM(c.firstname) + ' ' + IIF(!EMPTY(c.mi),c.mi + ' ','') AS cname,; c.entrydate,; birthdate,; CalcAge(BirthDate) AS age,; sex,; PADR(GetCatCode(a.county,'County'),25) AS county,; PADR(GetCatCode(race,'Race'),25) AS race,; PADR(GetCatCode(c.status,'ClientStatus'),25) AS istatus,; UPPER(TRIM(lastname) + firstname + mi) AS ifield,; lcHeader AS header,; lcOwner AS owner ; FROM selectedlist s ; INNER JOIN clients c ; ON c.clientid = s.clientid ; LEFT OUTER JOIN addresslink l ; ON l.tableid = c.clientid AND l.sourcetable = 'C' ; LEFT OUTER JOIN address a ; ON a.addressid = l.addressid ; WHERE !EMPTY(birthdate) ; INTO CURSOR clist READWRITE ; ORDER BY 9GetCatCode:
FUNCTION GetCatCode *- Returns category code description (catname) when data environment does not *- include category code cursor named to cattype *- Also returns specified system table names *- First two parameters are: * categoryid,cattype (fields in category table) LPARAMETERS tcCode,tcCodeCategory,tlForceAsCategory *- third parameter lets us force use of category codes table even though cattype is same as a THISAPPTITLE_LOC table name LOCAL lnFieldLen,lnOldSelect,lcReturn,llWasOpen,lcKeyName,lnPresentKey,laTables[1],; lcDataBase =ADBOBJECTS(laTables,'TABLE') *- third parameter tlForceAsCategory lets us force use of category codes table even though cattype is same as a THISAPPTITLE_LOC table name IF tlForceAsCategory OR ASCAN(laTables,UPPER(tcCodeCategory),1,-1,1,15) = 0 && was table name passed as cattype? *- no, just get cat code in usual manner lnOldSelect = SELECT() =Open('category') lnFieldLen = LEN(category.cattype) IF NOT EMPTY(tcCode) && cattype categoryid lcReturn = IIF(SEEK(PADR(ALLTRIM(tcCodeCategory),lnFieldLen) + ALLTRIM(tcCode),'category','typeid'),; ALLTRIM(category.catname),; tcCode) ELSE lcReturn = '' ENDIF SELECT(lnOldSelect) ELSE && otherwise, table name was passed, go look up the appropriate name lcReturn = GetName(tcCode,tcCodeCategory) && function in Local.prg to return names from various tables ENDIF RETURN lcReturnand CalcAge:
FUNCTION CalcAge(tdBirthDate,tdRelativeToDate) LOCAL lnAgeDays,lnAgeYears,lnAgeMonths IF PCOUNT() < 2 OR VARTYPE(tdRelativeToDate) <> 'D' tdRelativeToDate = DATE() ENDIF lnAgeDays = tdRelativeToDate - tdBirthdate lnAgeYears = INT(lnAgeDays/DAYS_PER_YEAR) lnAgeMonths = ROUND(MOD(lnAgeDays,DAYS_PER_YEAR)/30.4375,0) RETURN ALLTRIM(STR(lnAgeYears)) + 'Yrs, ' + ALLTRIM(STR(lnAgeMonths)) + 'Mos'Cetin may be on to something with the datasession, although I don't see why that should cause a SYNTAX error???