Liveware Publishing Support Forum

The problem:____I am working with a database of names for a volunteer youth organization. The adults are required to have a Police Record Check (PRC) thus the data base contains a field to record the issue date of the PRC. For reasons that I won_t bore you with it is formulated as a text field!__ __As a PRC is valid for three years from issue date I created a calculated field that converts the text field (PRCDATE) to a calculated date field (PRC_DATE). Using the ADDYRS function I created a calculated date field (PRC_EXP) to display the expire date. If PRCDATE is blank the result for PRC_EXP is xx.xx.xxxx. I am attempting, with no success, to create a IIF calculated field that will return a blank entry or a text entry for PRC_EXP rather than the string of asterisks. ____To add to this dilemma I have another heading called PRC Status to display the current status within the three year period. The formula is as follows IIF(PRC_Expire>=wizDate, ^OK^,^Expired^). If the result is true OK appears, however if the result is false instead of _Expired_ I get *******.____Suggestions?

Try:____IIF(ERROR(PRC_EXPIRE),^Expired^,IIF(PRC_Expire>=wizDate,^OK^,^Expired^))____This will test for a error condition before doing the date comparison.____Kathleen__R&R Support

thanks____This worked find where I needed it. I^m still wrestling the the PRC_Expire calculated field --ADDYRS(PRC_DATE,3)-- which when PRC_DATE is blank returns **.**.****. As I interpret the Manuals instructions for ERROR, the formula IIF(ERROR(PRCDate),^ ^,ADDYRS(PRC_DATE,3))should work, however I get the invalad argument message. In this example PRCDATE is the database text field and PRC_DATE is the R&R calculated field representing it as a date field. I get the same result when I substitute PRC_DATE as the reference field.____Pete

In an IIF both of the return values need to be of the same data type.__Since you have a character string and a date, that is why you are in trouble.__To resolve this you can convert the date to a character string so that the expression would be__IIF(ERROR(PRCDate),^ ^,DTOC(ADDYRS(PRC_DATE,3)))____Regards,__Kathleen__R&R Support

I can follow the logic of the formula and understand why it should work, however it still returns a string of asterisks, in this case in the format of ********** instead of **.**.****. I changed the formula to reference the date field IIF(ERROR(PRC_Date),^ ^,DTOC(ADDYRS(PRC_DATE,3))) and get the same result.

Try:____IIF(ERROR(DTOC(ADDYRS(PRC_DATE,3))),^ ^,DTOC(ADDYRS(PRC_DATE,3)))

That one does it, I really appreciate your assistance...Thanks