• Registered: 2018-12-02
  • Posts: 151

Topic: Auto query filter to show today’s data

Hello everyone!
I am trying to create Form1 as noticeboard. I wish to show today’s appointment in the lower table grid. The data is in the table AA_009_Day_Appo. Can you please suggest the correct Filter to achieve the desired output?
Eyeman

Post's attachments

Attachment icon AUTO QUERY Filter .png 144.39 kb, 8 downloads since 2024-02-16 

2 Reply by pavlenko.vladimir.v (edited by pavlenko.vladimir.v 2024-02-16 16:13:16)

  • From: Нефтеюганск
  • Registered: 2022-06-03
  • Posts: 454

Re: Auto query filter to show today’s data

current_date replaced by - Date

  • Registered: 2018-12-02
  • Posts: 151

Re: Auto query filter to show today’s data

Hi Vladimir,
The filter AA_009_Day_Appo.Date_Appo='Date' gives no error message but no output in the table grid, even if there is appointment today. Date_Appo field type is Date.
Thanks,
Eyeman

4 Reply by sparrow (edited by sparrow 2024-02-16 19:21:03)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Auto query filter to show today’s data

date(Date_Appo) = CURRENT_DATE

Data of type DATE is stored as '2024-02-16 00:00:00.000'. And this is a text format.
When you set your condition, it turns out that '2024-02-16 00:00:00.000' = '2024-02-16' which is FALSE.
Therefore, DATE('2024-02-16 00:00:00.000') will highlight only the date and allow for a correct comparison.
Another possible solution would be to use LIKE.

  • Registered: 2018-12-02
  • Posts: 151

Re: Auto query filter to show today’s data

Hello Sparrow,
Your code works perfectly. Thanks a lot for the code and explaining it so clearly. Lesson learnt! However I tried unsuccessfully to get the desired output using LIKE operator. Any suggestion please?
Regards,
Eyeman

Post's attachments

Attachment icon Day's appointments.png 68.3 kb, 9 downloads since 2024-02-17 

6 Reply by sparrow (edited by sparrow 2024-02-18 10:27:26)

  • From: Рідна МАТИ, UKRAINE
  • Registered: 2018-06-02
  • Posts: 894

Re: Auto query filter to show today’s data

Date_Appo = CURRENT_DATE||' 00:00:00.000' - All entries for current day

SUBSTR(Date_Appo, 1, 10) = CURRENT_DATE - All entries for current day

DATE(Date_Appo) LIKE CURRENT_DATE - All entries for current day

Date_Appo LIKE '2024-02-18%' - All entries for current day months

Date_Appo LIKE '2024-02%' - All entries for this month

DATE(Date_Appo) BETWEEN DATE(CURRENT_DATE, '-7 day') AND CURRENT_DATE - All entries for last 7 days

https://www.sqlitetutorial.net/sqlite-like/

https://www.sqlitetutorial.net/sqlite-between/

https://www.sqlitetutorial.net/sqlite-in/

...

  • Registered: 2018-12-02
  • Posts: 151

Re: Auto query filter to show today’s data

Hello Sparrow,
Thank you for the excellent examples that illustrate how the LIKE, BETWEEN operators and Substring Function may be used. Thanks also for suggesting excellent web resource.
Regards,
Eyeman