Depends on what you compare with.
Open tbl_Filter of my https://ask.libreoffice.org/uploads/short-url/pcJkYxh7TT7NLsUmEtCnWQA28be.odb
Record FID=1 filters by text like “2025-01” stored in C1.
In the subform properties, tbl_Filter.C1 is linked to paramYearMonth of query “qry_WXdataLeeds_Filter”
This subform query splits the “2025-01” in two numbers and compares the resulting numbers with the years and months of the ReadDate:
WHERE
YEAR( "ReadDate" ) = CAST( LEFT( :paramYearMonth, 4 ) AS SMALLINT )
AND MONTH( "ReadDate" ) = CAST( SUBSTRING ( :paramYearMonth, 6, 2 ) AS SMALLINT )
CAST converts the leftmost 4 digits and the last 2 digits into small integers and compares them with Year(ReadDate) and Month(ReadDate) which are also to integers.
Of course you may also concatenate Year(ReadDate) with 2-digit Month(ReadDate) and compare the resulting strings with each other. That would be:
WHERE
Year("ReadDate") || Right('0' || Month("ReadDate"), 2) = :paramYearMonth
We need a parameter query because we can not compare the VARCHAR filter criterion with anything stored in the database table directly. We need to do some kind of conversion in this or that direction, either split a string into 2 numbers or concatenate 2 numbers into one string.
In the next variant, record FID=2 stores 2 integer numbers in INT1 and INT2.
The subform query compares the two numbers with the years and months of ReadDate:
WHERE YEAR( "ReadDate" ) = :paramYear
AND ( :paramMonth = 0 OR :paramMonth = MONTH( "ReadDate" ) )
The listbox selects distinct year numbers from “ReadDate”. The numeric form control allows for numbers 0 to 12 and. In case of zero, the query returns the entire year’s data.
The first comparison before the AND returns True if Year(ReadDate) equals the paramYear (taken from tbl_filter.D1, entered by means of the listbox).
The second comparison behind the AND returns True either if paramMonth (taken from tbl_Filter.D2 entered by means of a numeric control) is zero or if paramMonth equals Month(ReadDate).
Both comparisons are connected by AND, so a record is included in the filter result if both sides of the AND are True. (year is equal) AND (month is zero or equal)
In the third variant “Leeds Weather2” I store 2 date values in D1 and D2 and compare them directly with “ReadDate”
WHERE ( :paramD1 IS NULL OR "ReadDate" >= :paramD1 )
AND ( :paramD2 IS NULL OR "ReadDate" <= :paramD2 )
Both date controls (and their underlying tbl_filter.D1 and D2) are allowed to be empty. If both are empty, the comparisons on both sides of the AND return True. True AND True is True for all records, therefore the query returns all data unfiltered.
If only paramD1 (taken from tbl_Filter.D1) is empty, the first comparison returns True anyway while the second comparison returns True in case of ReadDate being smaller than or equal to paramD2 connected to tbl_Filter.D2.
If only paramD2 (taken from tbl_Filter.D2) is empty, the first comparison returns True in case of ReadDate being greater than or equal to paramD1 while the second comparison returns True anyway.
If none of the two parameters is empty, both parameters are compared with ReadDate and only those records are matched where D1 >= paramD1 AND D2 <= paramD2.
In this variant we need a parameter query because the two comparisons distinguish between given and missing values and because we compare by operators >= and <=.
Without any parameter query, we can only compare if 2 values are equal to each other:
tbl_Filter.D1 = tblWXdataLeeds.ReadDate
which returns no records if tbl_Filter.D1 is empty or if tbl_Filter.D1 does not occur in tblWXdataLeeds.ReadDate. You have to filter by a directly comparable value that occurs in the filtered table.
