Get single result from a separate query and put it into the report footer

[j.t.w]

I have a summary report that prints out the group name and the totals
for each group.

Vegetables $100.00
Groceries $200.00
Seafood $300.00
Misc $ 10.00
Discount $ 12.00
Over Ring $ 15.00
..
..
..
etc.

Grand Total $637.00

I would like to sum specific group amounts like Misc, Discount, and
Over Ring and then display it in the report footer of the report.

Misc Total $ 37.00

The specific groups are not in a particular order. I can get the
result that I want from a separate query.

I tried using the DLookUp function, but I get a #Error in my textbox.
txtMiscTotal Control Source is
=DLookUp("MiscTotal","RegTape_MiscTotal2_qry_rpt")

I know that the DlookUp function will work, but it's just not working
this time. I have a "RegTape_MiscTotal1_qry_rpt" that gets the
specific categories with a total. The "RegTape_MiscTotal2_qry_rpt"
then sums the "RegTape_MiscTotal1_qry_rpt" query. The first query is
looking at a date field from the "RegTape_qry_rpt" query, which is the
recordsource for the report.

I have also tried to open a recordset and read it into my textbox.

Private Sub Report_Open(Cancel As Integer)
Dim conn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim varAmount As Variant
Dim strSQL As String

'Set up the connection and recordset.
Set conn = CurrentProject.Connection
Set rst1 = New ADODB.Recordset

strSQL = "SELECT Sum([RegTape_MiscTotal1_qry_rpt].[SumOfRegCategoryAmount])
" strSQL = strSQL & "AS MiscTotal FROM RegTape_MiscTotal1_qry_rpt;"

'Open the recordset.
'rst1.Open strSQL, conn, adOpenKeyset, adLockOptimistic,
adCmdTableDirect
rst1.Open strSQL, conn, adOpenDynamic, adLockOptimistic

varAmount = rst1!MiscTotal
txtMiscTotal = varAmount

'Close the recordset and connection.
rst1.Close
conn.Close
Set rst1 = Nothing
Set conn = Nothing
End Sub

Sorry, for the long post. Any help will be greatly appreciated.

TIA,
j.t.w