By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,419 Members | 1,289 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,419 IT Pros & Developers. It's quick & easy.

variable in a report

P: 6
hello,

fyi:
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
======

Problem:
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

------------------------
The question is: How do I get the value of the variable iADJTotal in the field in the report?

thanks a lot!
May 30 '07 #1
Share this Question
Share on Google+
1 Reply

MMcCarthy
Expert Mod 10K+
P: 14,534
Look at creating group levels in your report and put Sum(Amount) in the group footer.
Jun 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.