I made a form for my data (no subforms, very simple but 550 records) with a couple image link fields. I didn’t do this under query forms, its just a data form. Context: this is a database for a coin collection, mostly from ebay purchases. In a text field I enter 2021 under the ebay listing title. I have 10 records with a coin dated 2021. I tried wild card: *2021 nothing. The filter navigator pops up as shown here in the pic. NOT one record shows up! Why? I am not a SQL language person, please help. This to me should be so intuitive the way I am doing it, numerous posts on reddit of similar users with this frustration. I used AI and no help. There are no background filters blocking “2021” records from being searched. Please explain simply, as I want a form view of my record and not raw tables as in a query. Thanks.
You are editing the filter, bur not executing. Press the button left from the button, which has just been pressed. It executes the filter.
Enter LIKE 2021* which means that the text starts with 2021.
Enter LIKE *2021 which means that the text ends with 2021.
Enter LIKE *2021* which means that the text contains 2021 anywhere.
Without the LIKE operator, the record set is filtered by the literal *2021* including the asterisks.
RobertG… that doesn’t work. I clicked on the icon that you were referring … lower left corner, right? I am not editing a form, you see it grayed out in the original post. I know the difference… just new to LibreOffice. Basically I want to do a search similar to searching on web page … using CTRL-F to find all records containing 2021. Thanks for replying tho!
That is “starting with 2021”. What is the column type of the column “Listing Title”? Is it a date?
If “Listing type” happens to be a date column:
- Edit the form properties.
- Switch from content type “Table” to “SQL”.
-
SELECT *. Year("Listing type") AS "Year" FROM "table_name"
Replace “table_name” with the actual name of the table. Replace “Listing type” with the actual name of the table’s column.
This selects the whole table adding a column with the year numbers which can be filtered easily.
Do it like this:
You could write
LIKE %2021%OR
LIKE *2021*will be changed to what you could see in the screenshot.
It couldn’t be a field special for dates, because you will get an error when trying to execute the filter:
Thank you!! It works.
What exactly works?
Ok, end user here and trying to keep it simple. This is a coin collection and the listing title has most of the descriptors. I do find the icons confusing … they’ve changed from early youtube tutorials. I like the old, familiar “run!” Or “go!” But the explanation I tried … putting “Like” in front of the criteria.
Enter LIKE 2021* which means that the text starts with 2021.
Enter LIKE *2021 which means that the text ends with 2021.
Enter LIKE 2021 which means that the text contains 2021 anywhere.
Do I need “AND” if I add a second criteria in the same field? For example 1878* AND *Morgan dollar
Try it:
LIKE %2021% AND LIKE %or%
returns all rows with ‘2021’ and ‘or’ anywhere in the string.
Entry will be changed to
LIKE '%2021%' AND '%or%'
by the GUI.
I tried to different searches, none worked. There are several Franklins and some are proof (PF) and some mint state (MS). I wanted only the PF records. You can see the table right next Filter navigator that shows the records. Nada. The other thing I don’t like, being case sensitive. Why doesn’t it take “FRANKLIN” and “Franklin” and “franklin” … sort of defeats the purpose of a database if I have to go find the record manually to see what case I typed the letters in. This is turning into a very difficult project that shouldn’t be that hard.
My LibreOffice version is:
Version: 25.8.4.2 (X86_64)
Build ID: 290daaa01b999472f0c7a3890eb6a550fd74c6df
CPU threads: 4; OS: Linux 6.12; UI render: Skia/Raster; VCL: x11
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded
The attached database document contains an embedded HSQL database (see status bar). All strings are of column type VARCHAR, which is case-sensitive.
The first form “Combo Filter” is a form/subform combination where you enter a search string, click OK and the subform shows street names containing the entered search string.
- Keep that search string in the main form empty. This shows all the records in the subform.
- Turn on form based filter on the subform and enter:
like A* and like *e(starting with upper case A, ending with lower case e). - Apply that filter and confirm that it works as intended.
BerlinStreets_LO7.odb (630.1 KB)
@kculp : Have a look at the code:
LIKE '%Franklin%' AND LIKE '%PF%'
When reopening the form based filter the second LIKE has been removed and it only looks for the string ‘%PF%’ - but there is no field in “Listing Title” which only contains ‘%PF%’.
Removing the second LIKE is a bug of the form base filter.
BerlinStreets_FilterMacro.odb (642.2 KB)
Have a look at this form. Sometimes a less elaborated tool is better. IMHO, the fundamental SQL syntax for filter conditions is easy to use, even for beginners.
The column type of the names in the table is VARCHAR_IGNORECASE. Within the filter expressions, only the double-quoted column names are case sensitive. "Name" like 'a%' and "Name" like '%e matches names starting with ‘A’ or ‘a’ and ending with ‘E’ or ‘e’.
Substring("Name",2,1)='a' matches names having ‘a’ or ‘A’ as second letter.
Instead of a text box, you may use a combo- or list box with pre-fabricated filter expressions.
Sorry you are confusing me more. What is the “intended” supposed to mean? All I get is the screen below. I see some databases SQL ignores case with “ILIKE” instead of “LIKE” so I tried it. Not supported. Sad.
Thank you, I get it. Didn’t know you needed to repeat like…Appreciate your help!
Your screenshot is taken from the wrong form. Open the form “Filter String” of database document BerlinStreets_FilterMacro.odb
My intension was, to create a little input box taking arbitrary filter expressions in plain, simple SQL syntax that won’t be corrupted by a sophisticated tool like “form based filter” which does some corrections and translations to your criteria input.
The following attachment is another sample with a combo box. This is how I created this one:
- Copied the Basic module into a new Writer document.
- Adjusted the page style and switched to web view.
- Added a filtering form with a combo box, a toggle button and a hidden field named “Filtered Form” pointing to the filtered form.
- Name of the combo box to the button’s “Additional info”.
- Added another form with a table control and the other two hidden fields “Toggle Button” and “Filter String” pointing to the other form’s button and combo box. This form is bound to table “biblio” in databse “Bibliography” shipped with LibreOffice.
- Assigned macros to the form event “When loading” and the button’s “Item status changed”
- Tested several filter expressions and added them to the combo box.
The first oneLCASE("Title") LIKE '%%'searches case-insensitively within the the titles if you enter something between the two %%.
The ones below search for names of office components in the book titles.
Biblio_FilterString.odt (32.5 KB)