Home  Fees/Services  Access Examples  Tutorials  Access Download  Articles  Search  Contact  Privacy  Links

Access Query Examples
Restrict Report Records
Filter Report Query with Form Fields

Programmable Visual Basic & SQL Report Filters SQL Queries

Access Database Tutorials>SQL Queries>Filter Report Records

Download Access Program


Aggregate Functions
Child/Parent Table Query
Choose Function
SQL Choose Function +
Crosstab/Pivot Query
SQL Crosstab Query +
DateTime Query
SQL Delete Query
External Link to Tables
Filter Report Records
Group By Clause
Having Clause Query
Histogram Query
Insert Into Query/Append
Master/Detail Updates
SQL Order By Dynamic
Parameter Query
SQL Predicate Example
Self Join Query
Access Scalar Query
Select Statement
Select Top 1 Query
Union Query Example
Update Query Example


Filter Report Records via Form Field

Use Form Fields as Parameters in a Query

In this filter report records via form field example we use the Employee_ID selected in filter report example to select employee records for a report.

Many users and developers would use filters on the report to accomplish this task.

Filters are ok to use but putting these restrictions in the SQL query for the report usually runs faster.  This example assumes that you have a combo box (named: Emp_Combo), as defined in a previous example, in a form called F_Emp.

Filter Report Records Using Form Field Parameter Example:

Select M_Employees.Name, M_Employees.Emp_Number, M_Employees.Address
From M_Employees
Where
Forms!F_Emp!Emp_Combo=0 or Forms!F_Emp!Emp_Combo = M_Employees.Employee_ID;

This SQL query will select a specific employee or All employees for the report based on the Emp_Combo in the parameter form (F_Emp).  Note: You could have several combo boxes on the parameter form which would allow the user to select Employee records based on a combination of several fields.   This filter report records method provides a very powerful reporting feature.  (Warning:  Access may attempt to re-write your 'where' clause making it non-functioning).

Warning: If you ever want to upsize this Microsoft Access database to SQL Server then you should not refer to form fields within the query.  SQL Server can't deal with these references. 

More Filtering Query Examples:

Parameter Query Examples

Attendance_Date >= Forms!F_Emp_Report!Start_Date Group By Employee_ID ... Note: If you get an Microsoft Access error saying that the form or field Forms! ...
www.blueclaw-db.com/accessquerysql/parameter_query.htm

Access Database Download: Dependent Combo Box Example

Advisor_ID)=[Forms]![F_Combo]![Advisor_Combo])); ... Continuous-Continuous Master/Detail Forms ... Bar Charts/ Bar Graphs on Access Forms ...
www.blueclaw-db.com/download/dependent_combo_box.htm


 

We now offer web-based SQL/Server application development in partnership with Integrated Databases, Inc. (new projects only).



Contact Information

SQL Queries
MS Access SQL Queries 2007 2003 2000  All Windows Versions