Libreoffice Base Query LIKE is not working

Hello, recently I update OS to Zorin 17 so it seems that Libreoffice is updated as well. However, trying to make old query working is not successful. I have a table with the “Subject” field is mixed character case, capital & SMALL, therefore, previously I use a filed LOWER ( “Subject” ) and put the LIKE statement on criteria to get the required result. suddenly the like statement is returning error “Like cannot be used with this field”. However, it is working in the SQL view only. Moreover, the query can be saved only in SQL view. I have screenshots for your reference.

  1. Which database do you use? Internal HSQLDB, internal Firebird or what else?
  2. Add screenshot from table properties (open table for editing → mark field “Subject”)
  3. Add screenshot from the error, which returns when executing the query.
  1. I am using HSQLDB and the file is located on my portable Harddisk.
    1. in the following posts

Please inform for additional information
Tarek Gamil

Query runs in SQL-view, but has problems with GUI. Might be this appear with the useless ‘(’ and ‘)’ in the condition. But you could use the query the same in a form as a query, which works when editing in GUI, because the query isn’t set to direct SQL.

If creating queries I start often with GUI, then change to SQL and never go back to GUI. Only important is the difference between “Execute” and “Execute in direct SQL”, which isn’t activated in your query.

I am always start the query in GUI as I am Microsoft Access user and I am not used to SQL at all. In fact this the first time I use a SQL statement and I used it as a trial work around to the error that I am receiving while used the GUI. Therefore, please help to fix the error of GUI as currently I need to use SQL statement if I will be able to get any working query.

Have tested this with internal HSQLDB. Use LCASE instead of LOWER. Seems LCASE is supported by GUI.
Have written a bug description for this:
https://bugs.documentfoundation.org/show_bug.cgi?id=159588

1 Like

This is working OK. Thanks.