Well, here's part trivia, part navel gazing, part bragging...but you can do nested EVALUATE()s in a field expression within VFP SQL. And it can make sense.

I was converting some old procedural code in VFP 7 to a SQL call where there were multiple conditions for a person's age at a given event. The prior code checked if the age was less than zero, make it zero (data entry errors, not real life), if the person was 100 or over, use 99, if there's no birthdate, indicate Unknown, otherwise calculate age.

There was a recent ProFox thread about dealing with either MAX() or MIN() within SQL - it doesn't work. Well, the SQL MAX function works in VFP SQL but not the VFP MAX function. I had a tip to the FPA a long time ago that if you use the EVALUATE() function in VFP SQL, it would run the VFP MAX() function and not the VFP SQL MAX() function and that was your workaround.

So with that I put this together for the calculation as a field expression

IIF(NOT EMPTY(<birth_date>), ; PADL(EVALUATE("MAX(INT(EVALUATE([MIN(((<event_date> - <birth_date>) / 365.25), 99)])), 0)"), 2, [0]), [UK]) AS cAge

...where <> represents the appropriate date field in a table.

Not only does it work, it appears to be fast.

Bill Anderson

--- StripMime Report -- processed MIME parts --- multipart/alternative text/plain (text body -- kept) text/html ---