Others

Automatically Display a Report Type in Microsoft Access

Algirdas JasaitisAlgirdas Jasaitis Nov 14, 2026 869 views

Question details

The user wants Access to display a report type when a related report-name field contains text.

Automatically Display a Report Type in Microsoft Access
Product
Microsoft Access
Device & OS
not provided
Scenario
Ad-Hoc and Production report names are stored in separate fields, and the user wants an Ad-Hoc label when an Ad-Hoc report name is entered.
Observed behavior
The desired result is for the report type to appear automatically without unnecessarily duplicating data in the table.
Before you start

Identify the field that contains Ad-Hoc report names and confirm whether the label is only needed for display or must be stored as a separately selected report type. Replace the example field name in the query with your actual field name.

Solution 1Recommended

Display Ad-Hoc with a calculated Access query field

Use this approach when the existing Ad-Hoc report-name field identifies the type and you only need to display the label.

1
Open Query Design

In Access, select Create > Query Design and add the table that contains the Ad-Hoc report-name field.

2
Add the report-name field

Double-click the Ad-Hoc report-name field to add it to the query grid.

3
Add a calculated type

In an empty Field cell, enter ReportTypeDisplay: IIf(Len(Trim(Nz([AdHocReportName],"")))>0,"Ad-Hoc",Null). Replace AdHocReportName with the actual field name.

4
Run and save the query

Select Run to check that rows with a nonblank Ad-Hoc name display Ad-Hoc. Save the query and use it as the record source for a form or report if needed.

Display Ad-Hoc with a calculated Access query field
The label is not stored: The calculated field displays a value in the query; it does not write Ad-Hoc to the table. It also does not determine a Production label.
Free Microsoft Office alternative

Work with Report Lists in WPS Spreadsheets

WPS Office is a free, lightweight alternative with a familiar interface for working with Office documents. Use WPS Spreadsheets for report lists in compatible Excel files and move those files between Office suites easily. It does not replace Microsoft Access for managing Access databases.

Organize report names and types in spreadsheet columnsOpen and edit Microsoft Excel XLSX filesUse a familiar spreadsheet interfaceWork with a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Does the calculated query field store Ad-Hoc in my table?

No. The expression displays Ad-Hoc in the query result when the specified field contains nonblank text; it does not update a table field.

What happens if the Ad-Hoc report-name field is empty?

The example expression returns Null when the field is Null, empty, or contains only spaces. Nz and Trim handle those cases before the length is checked.

Can I show the calculated type on an Access form?

Yes. Save the query and set it as the form's Record Source, then add a control bound to the ReportTypeDisplay query field.

Should I label every nonblank report name as Ad-Hoc?

Only if that field contains exclusively Ad-Hoc report names. If one ReportName field holds multiple types, record the type separately rather than inferring Ad-Hoc from the presence of a name.