Help with togglefilter for Listbox
Win10 LO6.4 HSQL2.51
Hello all,
I am stuck with putting a simple filter together.
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.
Hello,
The comparison you are looking for is:
RIGHT(EXTRACT ( YEAR FROM CURDATE() ), 2)
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 , 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.
@glick,
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! 
(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!