Hello All,
I am a new user attempting to migrate from Kexi from MS-Access. Currently I am unable to figure out how I can query columns with the criteria expression equivalent of IS NOT NULL. That is I have some columns in a table with empty entries and I want a query to return just the rows that actually have values for one of the rows. Currently criteria NOT NULL gives me the error 'Invalid criteria "NOT NULL"'. Null and one double quote cause the query to return no rows at all. In short, I am at a loss. I'd appreciate any suggestions anybody can offer. Thanks!

Hi ohnoplus,
It's nice to see you're trying Kexi to recreate your database. I'll try to help as much as I can.

In databases empty fields can be of two types: real NULL values or empty string of zero length, denoted as '' in SQL language. ('' is 2 x single quote, please remember). I am not sure to what category your "empty" values belong, it's also possible that to both

1. If they are empty strings, you can enter this to your criteria field in the visual query designer:

<> ''

2. If they are NULLs, you cannot enter IS NOT NULL to your criteria field in the visual query designer because this is not yet supported... for this to work you'd need to go to the SQL view by clicking the SQL View icon and add suitable criteria: WHERE fieldname IS NOT NULL, eg:

SELECT * FROM cars WHERE name IS NOT NULL

3. If they are NULLs or empty strings, act as explained in #2 but add two conditions, eg:

SELECT * FROM cars WHERE name IS NOT NULL AND name <> ''

Hope this helps. I am not sure you're able to work with SQL directly. Optionally you can copy-paste your SQL query here we'll see how it can be recreated.

Just please note that if you want to see the result editable, my earlier answer is more practical: do not use queries but in the Data Table View is: sort the column. Empty values should go to the top. Alternatively use the Find dialog as explained in the previous answer.