IIF and empty fields

Hi, apologies if this is a silly mistake on my part, but... I'm having trouble with working out if a field is empty

I've been trying to use "SELECT newfield = IIF(IsEmpty(table.field), 0, 1) ... " to return a bit value of whether the string field has something in it..

The syntax check says that IsEmpty is not a valid function - but I dont understand how it can be... am I missing something here?

I also tried using IIF(table.field = '', 0, 1) and that returns "invalid syntax near ="

Please help because its really confusing me, ThanksHi,

IsEmpty and IIF are Analysis Server functions, so you can't use it in reqular T-SQL. I don't seen any indication in your message that your using OLAP, so I'll assume you're using regular T-SQL with relational data.

What do you consider to be empty? A null value? Or what is commonly considered to be no data for a particular datatype (empty string for strings, zero for numerics, etc.)?

If nulls, consider using the ISNULL function. If the value is null it returns the value of your choice, otherwise the non-null value. The NULLIF function can help in some cases.

But if empty is an empty string or zero, etc., you can compare those values. Or use the CASE block, which lets you return different values based on various conditions.

I suspect that you'll find what you need in these T-SQL features.

Don|||ok, thanks a lot. explains why its not working anyway :)