2

I have an access Form which I am using to filter a query by referencing the Form Fields in the Query Criteria.

When the form is opened, the user can select specific entries from the below field drop down lists.

However, where the user doesn't select an entry, let's say they leave the Business Unit blank, I would like the query to return all entries for that field which the user didn't input data for, for example it would return all Business Units.

I tried entering a star (*) as per the below, and also tried leaving the fields blank to get it to return all entries but it didn't work.

Could you please advise how to get a query to display all records for any fields which aren't populated by a user in a form?

Thanks

enter image description here

The Query I am referencing is below:

enter image description here

The SQL of the query is below

SELECT [Accruals Raw Data].Company, [Accruals Raw Data].[Business Unit], [Accruals Raw Data].[Accrual ID], [Accruals Raw Data].[Accrual Sub Category], [Accruals Raw Data].[Posted Date], [Accruals Raw Data].[Amount $], [Accruals Raw Data].[User Name]
FROM [Accruals Raw Data]
WHERE ((([Accruals Raw Data].Company)=[Forms]![Filtered Accrual Reporting]![Company])) AND (([Accruals Raw Data].[Business Unit])=[Forms]![Filtered Accrual Reporting]![Business Unit]) AND (([Accruals Raw Data].[Accrual ID])=[Forms]![Filtered Accrual Reporting]![Accrual ID]) AND (([Accruals Raw Data].[Accrual Sub Category])=[Forms]![Filtered Accrual Reporting]![Accrual Sub Category]) AND (([Accruals Raw Data].[User Name])=[Forms]![Filtered Accrual Reporting]![User Name]));
Gustav
  • 53,498
  • 7
  • 29
  • 55
JDT
  • 107
  • 3
  • 14

1 Answers1

1

One solution might be to replace the equality operator in WHERE clause by a LIKE operator combined with wildcards characters.

This way if the values are empty it does not restrict your search.

LostReality
  • 657
  • 2
  • 8
  • 33