'------------SQL für Berechnung
newJahr2 = Replace(newJahr, neujahr, oldJahr3)
oldJahr2 = Replace(oldJahr, neujahr, oldJahr3)
newJahr1 = Format$(newJahr, "\#mm\/dd\/yyyy\#")
oldjahr1 = Format$(oldJahr, "\#mm\/dd\/yyyy\#")
newJahr2 = Format$(newJahr2, "\#mm\/dd\/yyyy\#")
oldJahr2 = Format$(oldJahr2, "\#mm\/dd\/yyyy\#")
'----------Tabelle monatsumsatz
Set mon1 = New ADODB.Recordset
mon1.CursorLocation = adUseClient
mon1.Open "Delete * from monatsumsatz", conn, adOpenKeyset, adLockOptimistic
'-----------Tabelle rechoff
Set kdrs = New ADODB.Recordset
kdrs.CursorLocation = adUseClient
kdrs.Open "Select * from rechoff WHERE redatum >= " & newJahr2 _
& " AND redatum <= " & oldJahr2 _
& " ORDER by kdnr ASC", conn, adOpenKeyset, adLockPessimistic
If kdrs.RecordCount >= 1 Then
anzahl = kdrs.RecordCount
End If
'---------Anzahl der Rechnung
If kdrs.RecordCount >= 1 Then
kdrs.MoveFirst
kdnr1 = kdrs.GetRows(, , "kdnr")
kdrs.MoveFirst
End If
'Loop
If Len(txt_PARAM1) = 0 Then
z = 0
'---------Routine Kundennummer in den Rechnungen feststellen
kdnr = UBound(kdnr1, 2)
For i = 0 To kdnr
kdnr2 = kdnr1(0, i)
If Not kdnr2 = kdnr3 Then
kdnr3 = kdnr2
z = z + 1
kdBerech = berech(kdnr3)
End If
Next
Else
kdBerech = berech(txt_PARAM1)
End If
cmd_Beenden.Visible = True: Label4.Visible = False: Label6.Visible = True
cmd_drucker.Visible = True
End Function
Private Function berech(kd_nr As Variant) As String
If kd_nr <> "" Then
'----------Tabelle monatsumsatz
Set mon1 = New ADODB.Recordset
mon1.CursorLocation = adUseClient
mon1.Open "Select * from monatsumsatz", conn, adOpenKeyset, adLockOptimistic
'-----------Tabelle rechnung
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT Sum(gespreis) AS NBetrag From rechnung " _
& "WHERE rechnung.kdnr= " & "'" & kd_nr & "' AND rechnung.rechDat >=" & newJahr1 _
& " And rechnung.rechDat <=" & oldjahr1 _
& " And storno = false", conn, adOpenKeyset, adLockReadOnly
If rs.RecordCount = 1 Then
summe = IIf(IsNull(rs!nbetrag), 0, rs!nbetrag)
summe = summe
summe2 = (summe / 100) * 19
summe = summe + summe2
UProzent1 = Val(summe)
mon1.AddNew
mon1!monat1 = Round(summe, 2)
mon1!von = txt_PARAM2: mon1!bis = txt_PARAM3
mon1!kdnr = kd_nr
summe = 0
m = 1
End If
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Open "SELECT Sum(gespreis) AS NBetrag From rechnung " _
& "WHERE rechnung.kdnr= " & "'" & kd_nr & "' AND rechnung.rechDat >=" & newJahr2 _
& " And rechnung.rechDat <=" & oldJahr2 _
& " And storno = false", conn, adOpenKeyset, adLockReadOnly
If rs.RecordCount = 1 Then
summe = IIf(IsNull(rs!nbetrag), 0, rs!nbetrag)
summe = summe
summe2 = (summe / 100) * 19
summe = summe + summe2
n = 1
End If
'-----------
If m = 1 Or n = 1 Then
UProzent2 = Val(summe)
UProzent2 = (100 * UProzent1) / UProzent2
UProzent2 = (UProzent2 - 100)
mon1!oldlahr = oldJahr3
mon1!neujahr = neujahr
mon1!Mon = thisMonth
mon1!mon1 = thisMonth1
mon1!monat2 = Round(summe, 2)
mon1!von = txt_PARAM2: mon1!bis = txt_PARAM3
mon1!kdnr = kd_nr: mon1!differenz = UProzent2
summe = 0
mon1.Update
End If
End If
End Function
