Solved Unable to Filter Query (1 Viewer)

Pac-Man

Active member
Local time
Today, 20:34
Joined
Apr 14, 2020
Messages
408
Hello,

I am trying to filter a query using a textbox on a loaded form and I am using following SQL but it is not working.

SQL:
SELECT tblReports.*, tblReports.ReportTitle
FROM tblReports
WHERE (((tblReports.ReportTitle) Like '* & Forms![frmSearchReport]![ReportTitle] & *'));

I am using this query on a subform and using me.SubFormControlName.requery in on change and after update event to filter the results. I want to implement search option so that user can search any text in the report title. Please help.

Best Regards,
Abdullah
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:34
Joined
May 7, 2009
Messages
19,169
Code:
...
...
WHERE (((tblReports.ReportTitle) Like "*" & Forms![frmSearchReport]![ReportTitle] & "*"));
 

Pac-Man

Active member
Local time
Today, 20:34
Joined
Apr 14, 2020
Messages
408
Code:
...
...
WHERE (((tblReports.ReportTitle) Like "*" & Forms![frmSearchReport]![ReportTitle] & "*"));
Thanks. It works. But it work only with after update event i.e. when I leave the text box or press enter key. Why is it not working with on change event so that it searched as I type. I used the same code in both events i.e me.SubFormControlName.requery

PS. I'm not saying it's with the SQL because query work fine but event is not working.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:34
Joined
May 7, 2009
Messages
19,169
save the Text property to variable:
Code:
Dim strText As String
strText = Me![ReportTitle].Text
...
...
WHERE (((tblReports.ReportTitle) Like "*" & strText & "*"));
 

Pac-Man

Active member
Local time
Today, 20:34
Joined
Apr 14, 2020
Messages
408
Perhaps try the "Key Up" event
Thanks for reply but it didn't work either. However I called the update event in key up and on change event and it worked in both (seems strange, I don't know why)
 

Pac-Man

Active member
Local time
Today, 20:34
Joined
Apr 14, 2020
Messages
408
save the Text property to variable:
Code:
Dim strText As String
strText = Me![ReportTitle].Text
...
...
WHERE (((tblReports.ReportTitle) Like "*" & strText & "*"));
Thanks for reply. Since I'm using query design to make the query and criteria (not VBA) so I can't use dim method to define variable.
 

Users who are viewing this thread

Top Bottom