passing a variable to a report

[Guest]

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

------------------------
I want iADJTotal to be the field in the report. any suggestions?

another issue: this report is printed for either all names or one.

thanks a lot!

 

[Guest]

Here's my ideas:

Q1? "In the report, I want the detail to show for the chosen TransType(s),
name, and date."

** I would put 3 unbound textboxes in the report and source each one to the
form info
i.e.
=Forms![FormName].[TextBoxName]

of course the Form must be open when the Report opens - so that the Report
can find this info and insert it into the Report txt box.....I use this
technqiue all the time....


Q2? "....AND I want a total of the "ADJ" records for the specified name and
date to show in a field in the footer"

** On this I will admit that I did not review your code....I think an MVP
could do VBA in an event of the Report to count those....while I might take a
more crude approach and count those in a separate query and insert a
subReport with the info because my use of loops is a little rusty.....


Q3? " another issue: this report is printed for either all names or one. "

** Do not understand this question......

--
NTC

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

------------------------
I want iADJTotal to be the field in the report. any suggestions?

another issue: this report is printed for either all names or one.

thanks a lot!

 

[Guest]

Q1? "In the report, I want the detail to show for the chosen TransType(s),
name, and date."




Q3? " another issue: this report is printed for either all names or one. "

** Do not understand this question......
thanks!!

 

[Guest]

Q1? "In the report, I want the detail to show for the chosen TransType(s),
name, and date."

*** Right - your form is open...so the report should open and include that
info that was entered into the form's text boxes...this should work fine...no
need to have the form open or anything like that....

Q3? " another issue: this report is printed for either all names or one. "

** Do not understand this question......

*** The description makes sense. Is there a question? perhaps you include
this for completeness - which is ok.....I don't see that this fact has any
change for your first question.

 

[Guest]

*** The description makes sense. Is there a question? perhaps you include
this for completeness - which is ok

the most important question I had on this was your 2nd question. anyone have
a solution for me??

thanks.

 

[Guest]

figured it out-- I made a UDF in that field and passed the form's parameters
to it. FoxPro and SQL are so much easier. :-o