Help with toggle filter for List box

Help with togglefilter for Listbox

Win10 LO6.4 HSQL2.51

Hello all,

I am stuck with putting a simple filter together.

image description

The attached image shows a list box displaying card payment codes
where the first 2 digits identify the month, the next 2 digits the year and the text the card.

Above the table control I want a button to filter the list by current year or show all.
I know I can extract the 20 or 21 etc. via left 3,2 but how do I match this up with the year of CURDATE().

Thanks for any pointers.


The comparison you are looking for is:


You can have an Option button or checkbox to have a macro move the SQL into the list box list content → no Where condition for ALL records or withe the WHERE for your current year selection.

@Ratslinger, thank you! Hope all is well in your end of the woods…


All is fine for now. Keep safe & healthy.

@Ratslinger , oops - the first part works Ok but I need to convert the year bit to a string since I get a incompatible error.

SELECT “ct”,“cmy” FROM “tblcmy” “tblcmy” WHERE SUBSTRING(“ct”,3,2) = RIGHT(EXTRACT ( YEAR FROM CURDATE() ), 2)

Tried CAST and TO_CHAR in vain, would it be easier to convert the first part to an integer ?


Works in Embedded not in 2.5. Just tested this and it works:

Where  SUBSTRING("ct",3,2) = TO_CHAR( CURDATE(), 'YY')

Thanks again, trouble is the db is 2.51, will research a little on conversion functions.


Not certain why you are having a problem. Although I only tested with 2.5 above, I downloaded & tested with v2.51 and the result was still good. Even put together a form & list box and that worked also.

Don’t know where else to guide you.

@ Ratslinger - My humble apologies, its working, I was using the wrong db which doesn t have the table, guess I shouldnt do these things at 1am. And I read your …Works in Embedded not in 2.5


No need for apologies. My original answer was tested using only embedded and guessed it was good. You missed a table. Now look at the last two comments - one by myself & one by you. We each incorrectly noted the others user name incorrectly! :slight_smile: (Yours being closer than mine :))

@Ratslinger, thanks for all that and yet ohh Bummer, just realized my whole idea of limiting scrolling and weird order like 0120Citi,0121Citi,0220Citi was a dead end as the filter not only filters the list but also the records in the table control, grrrrr - but I have learned something!