get value from a query /set a variable for a report

[sbcaco]

I put this question in the forms area -- it is a form/report problem. - don't
mean to double post, but I should have put it in this area instead. sorry!

hello,

I have a report that shows the detail for 3 different values of a field
named "TransType": (values: pmt, adj, act).

The name, date, and TransType fields are set via a form. In the report, I
want the detail to show for the chosen TransType(s), name, and date. AND I
want a total of the "ADJ" records for the specified name and date to show in
a field in the footer.

strDocName = "Sales for 1 month"
Set qdf = dbs.CreateQueryDef("aqdftemp", strSQLComplete)
DoCmd.OpenReport strDocName, acViewPreview

I am getting the correct data - I just can't figure out how to get the total
for the field type 'adj' in a field in the footer. I tried this:

Dim rstRebates As ADODB.Recordset
Dim strSQLCompleteADJ As String
Dim con2 As ADODB.Connection

strSQLCompleteADJ = "Select sum([amount]) as ADJTotal "
'to print all Names:
If Me!chkAllorOne = False Then
strSQLSalesDate = "having Rebate_Transactons.SalesDate = """ &
Trim(txtSalesDate.Value) & """"
strSQLCompleteADJ = strSQLCompleteADJ & strSQLFromWhere &
strSQLGroupBy & strSQLSalesDate & strSQLOrderBy
Else
'to print one Name:
strSQLHaving = " HAVING "
strSQLName = " Rebate_Transactons.Name Like """ &
Trim(lboName.Column(0)) & "*"""
strSQLTransType = " AND Rebate_Transactons.TransType = ""ADJ"""
strSQLSalesDate = " AND Rebate_Transactons.SalesDate = """ &
Trim(txtSalesDate.Value) & """"
strSQLHaving = strSQLHaving & strSQLName & strSQLTransType &
strSQLSalesDate
strSQLCompleteADJ = strSQLCompleteADJ & strSQLFromWhere &
strSQLGroupBy & strSQLSalesDate & strSQLOrderBy
End If

Set rstRebates = New ADODB.Recordset
rstRebates.Open strSQLCompleteADJ, CurrentProject.Connection
iADJTotal = rstRebates("ADJTotal")
Set rstRebates = Nothing