Using IIF in an access select statement

[jdport]

I want to do a select, where if the query returns a blank result then it returns some set text, and otherwise it returns the result of the query...

select iff(isnull([table1.field]) or [table1.field]="","empty result",[table1.field]) from table1 left join table 2 on table1.field=table2.field where ID='someID';

I hope my question makes sense.

It seems to work if the result set has results, but if I get no results it refuses to return the "empty result" string.

Anybody know why?

Thanks!

 

[KLin]

iif(len(nz([table1.field], "")) = 0, "Empty Result", [table1.field]) As Result FROM table1 left join table 2 on table1.field=table2.field where ID='someID';

See if that works. nz() replaces a null value with any value you specify.

 

[jdport]

Thanks a lot, that worked! I knew I had to be close but I just couldn't seem to get it to work and google wasn't helping. 🙂