I am working on a report header where I would like to show the selected query parameters in a text field. To be specific, the user accesses a form, selects any combination of the available combo boxes, and then clicks a submit button to pass the chosen parameters to the query which will is used to generate the report.
In the form, the combo boxes display the records from a lookup query. For example, one of my parameters is Status. The row source for the Status combo box has a row source called qry_StatusLookup which is just a query of the Status table. This will show dynamic results in the combo box. The combo box column count is 2, and the column widths are set to 0;"1". This binds the ID column (not the name column) so that the ID number is passed to the query when the user submits their selections.
In my report header, I have the same fields that should reflect the parameters chosen in the form that get passed to the query. For example, the Status textbox in the header is setup with the control source as:
=DLookUp("[Status_Name]","tbl_Status","[Status_ID] =" & [Forms]![frm_ParameterSelection]![cboStatusLookup])
This shows the name of the parameter chosen instead of the ID number. When I submit the form, the values pass to the query and the report is displayed. If a Status was selected on the form, then it will appear successfully. However, if the user does not select a Status, the query will run successfully (by not filtering by Status), and the report is correct, but the header field that is supposed to show the Status parameter selected will display as #Error.
I have attempted to wrap the Dlookup in an NZ function, but this still displays #Error is the parameter is not selected from the form. But if the parameter is selected from the form then the status will display successfully.
Is there a problem with the syntax for my Nz function?
=Nz(DLookUp("[Status_Name]","tbl_Status","[Status_ID] =" & [Forms]![frm_ParameterSelection]![cboStatusLookup]),"")
Or, is an empty combobox value being passed to the query technically not null?
An error is not null, you're wrapping the DLookUp in the Nz, not the field, and the DLookUp returns an error if the field is Null
Try the following:
DLookUp("[Status_Name]","tbl_Status","[Status_ID] =" & Nz([Forms]![frm_ParameterSelection]![cboStatusLookup], 0))
(I'm assuming 0 doesn't occur in your Status_ID column. Alternatively, you can use Iif(IsNull([frm_ParameterSelection]![cboStatusLookup]),)