variable in 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

 

[KARL DEWEY]

I have a report that shows the detail for 3 different values of a field
named "TransType": (values: pmt, adj, act).
Am I reading this correctly in that a single field by the name of TransType
contains three sets of information?

If this is correct then you need to fix the structure of your database.

If I misunderstood you then please clarify for me.

 

[sbcaco]

I figured out how to do what I need to do.

there are 3 different types of records in my table. (pmt, adj, act)... I
don't think that constitues a need to change the structure of my database.
it's just a field with different values.

Thanks.

named "TransType": (values: pmt, adj, act).
Am I reading this correctly in that a single field by the name of TransType
contains three sets of information?

If this is correct then you need to fix the structure of your database.

If I misunderstood you then please clarify for me.
--
KARL DEWEY
Build a little - Test a little

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