BAQ calc field for "column is [not] null"?

I’m trying to create a calculated field of type bit that is true if a column is not null and false if it is null. What’s the correct syntax?

I simplified it to a BAQ with just one table, Part, and tried the obvious, Part.PartNum is not null. This produces an error, “Invalid syntax near keyword ‘as’.” That’s not a typo.

If I enter a literal 1 in the expression editor, it accepts the syntax.

This should work for the expression for your calculated field.

iif(Part.PartNum is null, 0, 1)

Here’s a related piece of shorthand I use a lot:


So if PartNum is null or is empty you get the empty value.

For when you don’t care if it’s null or empty, just that there’s no value.



1 Like