Try using the Len() function instead:
LOAD CANCEL_DATE, ORD_Number, ORD_REVENUE as TOTAL_ORDER_REV RESIDENT Data WHERE Len(Trim(CANCEL_DATE)) = 0;
There is a IsNull() function as well, but it doesn't work properly as far as I'm concerned in some x64 systems. The Len() as above will work just fine, and the Trim() function within will remove all leading and trailing blanks, if any.
Hope that helps.