Automatically Display a Report Type in Microsoft Access
Question details
The user wants Access to display a report type when a related report-name field contains text.

- 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.
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.
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.
In Access, select Create > Query Design and add the table that contains the Ad-Hoc report-name field.
Double-click the Ad-Hoc report-name field to add it to the query grid.
In an empty Field cell, enter ReportTypeDisplay: IIf(Len(Trim(Nz([AdHocReportName],"")))>0,"Ad-Hoc",Null). Replace AdHocReportName with the actual field name.
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.

Store report names and types in separate fields
Use this design when each report needs an independently recorded type, such as Production or Ad-Hoc.
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.

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.




