How can I translate the results of a dynamic query into a report?

Question

How can I translate the results of a dynamic query into a report?

Carol Davis 25

I have a table in MS Access with Medical Lab results. There are 25 fields of results. I have a dynamic query that allows changing the field to display in a query and it is working properly. I want to build a report that will allow the different fields to be displayed. Currently when I build a report based on the dynamic query the field name remains fixed while the dynamic query results changes the field name correctly. How can I do this?

0 comments

4 answers

Your answer

Answer 1

Use a fixed alias for the changing query column, bind the report text box to that alias, and update only the heading to show the selected laboratory field.

Recommended design: keep the report schema stable

Modify the SQL generated by the existing dynamic query so that whichever laboratory field is selected is always returned as the same alias—for example, ResultValue.

SELECT
    PatientID,
    TestDate,
    [Glucose] AS ResultValue
FROM LabResults;

When another result is selected, generate:

SELECT
    PatientID,
    TestDate,
    [Cholesterol] AS ResultValue
FROM LabResults;

The source field changes, but the output column remains ResultValue. Access SQL supports assigning a different result-column name with AS.^1^

Configure the report as follows:

Open the report in Design View.
Press F4 to open the Property Sheet.
Select Report, then open the Data tab.
Set Record Source to the saved dynamic query. A report’s RecordSource can be a query name or SQL statement.^2^
Select the result text box.
Set Control Source to ResultValue. The text box will now remain bound even when the underlying laboratory field changes.^3^
Give its heading label a clear name, such as lblResultHeading.

If the field selection is made on a form—for example, combo box cboResultField on frmLabSelector—set the report heading in its On Open event:

Private Sub Report_Open(Cancel As Integer)
    Me.lblResultHeading.Caption = _
        Forms!frmLabSelector!cboResultField.Column(1)
End Sub

Adjust the column number to the combo-box column containing the display name. The label’s Caption property controls its displayed text.^4^

Keep the selector form open while opening the report.

Alternative: rebind the report control at runtime

If changing the query to return a stable alias is not possible, change both the text box binding and its label when the report opens:

Private Sub Report_Open(Cancel As Integer)
    Dim fieldName As String

    fieldName = Forms!frmLabSelector!cboResultField.Value

    Me.txtResult.ControlSource = "[" & fieldName & "]"
    Me.lblResultHeading.Caption = fieldName
End Sub

This requires the selected field to exist in the report’s current RecordSource; otherwise Access reports that the field is not in the field list. Limit the combo box to the 25 known field names rather than accepting arbitrary user-entered names.

The alias approach is preferable because the report always sees the same structure: PatientID, TestDate, ResultValue, and any other fixed columns. Only the query decides which underlying result populates ResultValue.

References

Answer 2

DBG 11,801 Volunteer Moderator

When you say "dynamic" query, does that mean you're using a crosstab query? To create a dynamic report, you may have to resort to using some VBA code. Are you okay with that?

Carol Davis 25

Following is my dynamic query

Private Sub FieldNameQuerybutton_Click() 'Select field name from combo box

Dim strField As String

Dim strSQL As String

Dim qdf As DAO.QueryDef

   

' Check if a field is chosen

If IsNull(Me.SelectFieldCombo) Then

    MsgBox "Please choose a field first.", vbExclamation

    Exit Sub

End If

 

' Get the dynamically chosen field name

strField = Me.SelectFieldCombo





' Build the SQL string dynamically

strSQL = "SELECT [RecordNum], [ExamDate], [" & strField & "] " & "FROM [LabResultst] " & _

"WHERE ((LabResultst.ExamDate) Between Forms!LabResultsHistory0f!SelectStartDate And Forms!LabResultsHistory0f!SelectEndDate) And [" & strField & "] Is Not Null " & _

"ORDER BY [RecordNum]"

'"WHERE [" & strField & "] Is Not Null " & _

'"ORDER BY [RecordNum]"



' Option A: Update a saved query named "WaterTestFieldName0q" and open it

On Error Resume Next

Set qdf = CurrentDb.QueryDefs("LabResultsFieldName1q")

If Err.Number <> 0 Then

    ' Create the query if it doesn't exist

    Set qdf = CurrentDb.CreateQueryDef("LabResultsFieldName1q", strSQL)

Else

    ' Update existing query SQL

    qdf.SQL = strSQL

End If

On Error GoTo 0



' Open the query to display all data from that chosen field

DoCmd.OpenQuery "LabResultsFieldName1q", acViewNormal

End Sub

I understand how the ResultValue field (from the AI response) will solve my problem but I don't know how to incorporate it into my dynamic query. Any ideas?

Duane Hookom 27,030 Volunteer Moderator

It looks like you are returning three columns every time. I would add a fourth with the measure.

strSQL = "SELECT [RecordNum], [ExamDate], “”” & strField& “”” as Measure, [" & strField & "] " & "FROM [LabResultst] " & _  "WHERE ((LabResultst.ExamDate) Between Forms!LabResultsHistory0f!SelectStartDate And Forms!LabResultsHistory0f!SelectEndDate) And [" & strField & "] Is Not Null " & _  "ORDER BY [RecordNum]"  '"WHERE [" & strField & "] Is Not Null " & _  '"ORDER BY [RecordNum]"

This would allow you to use the value of Measure as a column heading or whatever in your report.

DBG 11,801 Reputation points Volunteer Moderator

2026-10-31T16:30:34.2266667+00:00

As Duane said, since you're only selecting one field for the dynamic query, it's not as complicated as I thought compared to selecting multiple fields or using a crosstab query where the column values change based on the data in the table.

To implement the ResultValue suggested by the AI, you can adjust your SQL as follows:

strSQL = "SELECT [RecordNum], [ExamDate], [" & strField & "] AS ResultValue FROM [LabResultst] " & _

Then, in your report, simply change the Control Source of the last textbox to ResultValue. If you need to change the column header in the report, maybe you can also refer to the form's dropdown selection. For example, you can change the label into a textbox and use the following control source.

=Forms!FormName.SelectFieldCombo

Hope that makes sense...
Carol Davis 25 Reputation points

2026-11-05T13:31:36.72+00:00

I finally got back to trying your suggestions on 11/03/26. Adding the "As ResultValue" to the dynamic query resolves my problem. Thank you DGB for your help. All is right once again at this amature MS Access programming site.

Best Regards
DBG 11,801 Reputation points Volunteer Moderator

2026-11-05T17:25:37.0733333+00:00

Hi Carol. Glad to hear you were able to get it working. Good luck with your project.

Answer 3

Duane Hookom 27,030 Volunteer Moderator

There is an example of reporting a crosstab with unknown headings at http://access.hookom.net/Samples.htm

0 comments

Answer 4

Carol Davis 25

The response from DGB resolved my problem

0 comments