Hi there,

I have a problem with adding groups and totals to a datareport. I dont use the DateEnvironment but a query in the vb code. When i add a group in my data report i get the message: Report Sections do not match data source. I know how to fix that when you use a dataenvironment but not with te query in the code. Do i have to use SHAPE command or is there another possible solution? I cant figure it out, so can some please help me with this.

This is the query i use:

8 Public Sub overzicht_coupuregegevens(CoupNrAutomCmbo As String, CoupNaamCmbo As String, CoupNrMicroCmbo As String, CoupDepotpasCmbo As String, CoupCoupureCmbo As String, CoupDatumVan As Date, CoupDatumTot As Date, chk_StortDatum As Boolean, MaxRecords As Long, rs As ADODB.Recordset, StrTotaal As String, myOk As Boolean) ' gewijzigd 6 februari 2002
Dim Qry As String 'from clause
Dim QryM As String 'Main String
Dim QryT As String 'totalisering
Dim cmd As ADODB.Command
Dim cmdt As ADODB.Command
Dim autorisationStr As String
Dim Onreport As String
Dim Totaal As Double

If Initialisatie.TestConnection = 0 Then
Exit Sub
End If

On Error GoTo errorhandler

myOk = True
' QryM = " SELECT TOP.* FROM ("

QryM = QryM & "SELECT"
QryM = QryM & " decode(persoongegevens.naam,Null,'Geen persoon',persoongegevens.naam) as Naam"
QryM = QryM & " , persoongegevens.microsectienr as MSN"
QryM = QryM & " , decode(depotpasgegevens.kaartnummer,null,'Geen',depotpasgegevens.kaartnummer) as Depotpas"
QryM = QryM & " , decode(DPA_Gegevens.nummer, Null, 'Geen',dpa_gegevens.nummer) as AutomaatNr"
QryM = QryM & " , to_char(stortgegevens.datumtijd,'dd-mm-yyyy hh24:mi')as Datum"
QryM = QryM & " , decode(gestortecoupures.aantal,Null,0,gestortecoupures.aantal) as Aantal"
QryM = QryM & " , coupures.omschrijving as Coupure"
QryM = QryM & " , decode(gestortecoupures.aantal * Coupures.WaardeEuros,Null,0,gestortecoupures.aantal * Coupures.WaardeEuros) as Bedrag"

Qry = Qry & " FROM"
Qry = Qry & " Coupures"
Qry = Qry & " , gestortecoupures"
Qry = Qry & " , stortgegevens"
Qry = Qry & " , dpa_gegevens"
Qry = Qry & " , persoongegevens"
Qry = Qry & " , organisatorischeEenheid"
Qry = Qry & " , depotpasgegevens"
Qry = Qry & " WHERE"
Qry = Qry & " stortgegevens.dpa_id = dpa_gegevens.id (+)"
Qry = Qry & " and stortgegevens.depotpasid = depotpasgegevens.id(+)"
Qry = Qry & " and depotpasgegevens.persoonid = persoongegevens.id(+)"
Qry = Qry & " and persoongegevens.OrgEenhID = organisatorischeEenheid.ID(+)"
Qry = Qry & " and gestortecoupures.stortid = stortgegevens.id(+)"
Qry = Qry & " and gestortecoupures.coupureid = coupures.id(+)"

If Not CoupNrAutomCmbo = "" Then
Qry = Qry & " and DPA_Gegevens.Nummer like '" & SQLSafe(CoupNrAutomCmbo) & "%'"
End If

If Not CoupNaamCmbo = "" Then
Qry = Qry & " and Persoongegevens.Naam like '" & SQLSafe(CoupNaamCmbo) & "%'"
End If

If Not CoupNrMicroCmbo = "" Then
Qry = Qry & " and Persoongegevens.MicrosectieNr like '" & SQLSafe(CoupNrMicroCmbo) & "%'"
End If

If Not CoupCoupureCmbo = "" Then
Qry = Qry & " and Coupures.Omschrijving like '" & SQLSafe(CoupCoupureCmbo) & "%'"
End If

If Not CoupDepotpasCmbo = "" Then
Qry = Qry & " and Depotpasgegevens.Kaartnummer like '" & SQLSafe(CoupDepotpasCmbo) & "%'"
End If

If chk_StortDatum Then
Qry = Qry & " and Stortgegevens.DatumTijd >= to_date('" & Format$(CoupDatumVan, "dd-mm-yyyy hh:mm") & "',' dd-mm-yyyy hh24:mi')"
Qry = Qry & " and Stortgegevens.DatumTijd <= to_date('" & Format$(CoupDatumTot, "dd-mm-yyyy hh:mm") & "',' dd-mm-yyyy hh24:mi')"
End If

If getAutorisation(autorisationStr, Onreport) Then
Qry = Qry & " and (" & autorisationStr & ")"
End If

'samenstellen Main en FROM clause
QryM = QryM & Qry
QryM = QryM & " ORDER BY Stortgegevens.DatumTijd desc, Persoongegevens.Naam desc"
' QryM = QryM & ") TOP WHERE ROWNUM <= " & MaxRecords

Set rs = New ADODB.Recordset
With rs
.CursorType = adOpenStatic
.LockType = adLockReadOnly
End With

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = DataEnvironment1.Connection1
.CommandText = QryM
.CommandType = adCmdText
End With

Set rs.Source = cmd
rs.Open

If rs.RecordCount = 0 Then
Totaal = 0
StrTotaal = FormatNumber(Totaal, 2, vbFalse, vbUseDefault, vbFalse)
Else
Totaal = 0
rs.MoveFirst
While Not rs.EOF
Totaal = Totaal + rs!Bedrag
rs.MoveNext
Wend
StrTotaal = FormatNumber(Totaal, 2, vbFalse, vbUseDefault, vbFalse)
End If