Solved Filtering a Query from a Form (1 Viewer)

Pac-Man

Active member
Local time
Today, 15:33
Joined
Apr 14, 2020
Messages
408
Hello,

I'm trying to create a Query to filter using input from form frmSearch which contain Combo boxes of fields. These row source of these cbo is tblReports. I am trying out conditional filter using following expression in criteria:
=IIf(IsNull([Forms]![frmSearch]![ReportNo]), Like "*", [Forms]![frmSearch]![ReportNo])

I also used:
=IIf([Forms]![frmSearch]![ReportNo].[ListIndex]=-1), Like "*", [Forms]![frmSearch]![ReportNo])

What I want to achieve is query should filter the contents based on ReportNo from frmSearch and if that cbo in frmSearch is empty or not selected, it should use like "*" criteria and show all contents.

Where I'm doing wrong. Please help.

Best Regards
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:33
Joined
May 7, 2009
Messages
19,169
Code:
[fieldName]=IIf(IsNull([Forms]![frmSearch]![ReportNo]), [fieldName], [Forms]![frmSearch]![ReportNo])
 

Pac-Man

Active member
Local time
Today, 15:33
Joined
Apr 14, 2020
Messages
408
Code:
[fieldName]=IIf(IsNull([Forms]![frmSearch]![ReportNo]), [fieldName], [Forms]![frmSearch]![ReportNo])
Thanks a lot, it worked.
 

Pac-Man

Active member
Local time
Today, 15:33
Joined
Apr 14, 2020
Messages
408
Code:
[fieldName]=IIf(IsNull([Forms]![frmSearch]![ReportNo]), [fieldName], [Forms]![frmSearch]![ReportNo])
I have put all the filter in the Query using the mentioned expression except one. It is for date filtering. In the tblReports, each report has report date. On the frmSearch, there is cboYear which enlist years of report date only using following query as row source of cboYear:
SELECT DISTINCT Year([ReportDate]) AS [Year] FROM tblReports ORDER BY Year([ReportDate]);

How can I filter it so that if no year is selected in cboYear, it should use like "*" else selected year.

I tried this with no success:
Rich (BB code):
[ReportDate]=IIf([Forms]![frmSearch]![cboYear].[ListIndex]=-1, [ReportDate], [Forms]![frmSearch]![cboYear])
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:33
Joined
May 7, 2009
Messages
19,169
Code:
[ReportDate]=IIf(Trim([Forms]![frmSearch]![cboYear] & "") = "", [ReportDate], [Forms]![frmSearch]![cboYear])
 

Pac-Man

Active member
Local time
Today, 15:33
Joined
Apr 14, 2020
Messages
408
Code:
[ReportDate]=IIf(Trim([Forms]![frmSearch]![cboYear] & "") = "", [ReportDate], [Forms]![frmSearch]![cboYear])
Thanks for reply but this expression didn't work. I hope my question is not ambiguous. I put little more details. I have a cboYear on my form frmSearch with row source SELECT DISTINCT Year([ReportDate]) AS [Year] FROM tblReports ORDER BY Year([ReportDate]); which outputs year list in the combo box. I want to filter my query so that if any year is selected in the cboYear on the frmSearch then Query should be filtered by that selection otherwise it should treat as Like "*" in the criteria. One thing more, in the ReportDate field of tblReports, date is a complete date, not an year with format Medium Date.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 18:33
Joined
May 7, 2009
Messages
19,169
sorry, try this:

Code:
WHERE
Year([ReportDate]) = IIF(Trim([FORMS]![frmSearch]!cboYear & "")="", Year([ReportDate]), [FORMS]![frmSearch]!cboYear)
 

Pac-Man

Active member
Local time
Today, 15:33
Joined
Apr 14, 2020
Messages
408
sorry, try this:

Code:
WHERE
Year([ReportDate]) = IIF(Trim([FORMS]![frmSearch]!cboYear & "")="", Year([ReportDate]), [FORMS]![frmSearch]!cboYear)
Thanks for helping but still not working. However I found a way around. Posting it here if it may of help for someone else.

I defined new field in the Query with expression Year([ReportDate]) then used IIF([Forms]![frmSearch]![cboYear].[ListIndex]=-1, Year([ReportDate]), [Forms]![frmSearch]![cboYear] in the criteria.
 
Last edited:

Users who are viewing this thread

Top Bottom