VERSION 5.00 Begin {78E93846-85FD-11D0-8487-00A0C90DC8A9} ReportUmsatzbericht BorderStyle = 5 'Änderbares Werkzeugfenster Caption = "Berichte" ClientHeight = 6825 ClientLeft = 120 ClientTop = 390 ClientWidth = 5265 MaxButton = 0 'False MinButton = 0 'False ShowInTaskbar = 0 'False _ExtentX = 9287 _ExtentY = 12039 _Version = 393216 _DesignerVersion= 100684101 ReportWidth = 3825 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty GridX = 5 GridY = 5 LeftMargin = 200 RightMargin = 50 BottomMargin = 200 _Settings = 31 NumSections = 3 SectionCode0 = 1 BeginProperty Section0 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich4" Object.Height = 3075 NumControls = 12 ItemType0 = 7 BeginProperty Item0 {1C13A8E5-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Symbol1" Object.Width = 3742 Object.Height = 1065 SizeMode = 2 EndProperty ItemType1 = 3 BeginProperty Item1 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblTagesumsatz" Object.Top = 1474 Object.Width = 3742 Object.Height = 390 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 14.25 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Umsatzbericht" Alignment = 2 EndProperty ItemType2 = 3 BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblDatum" Object.Top = 1814 Object.Width = 3742 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Datum" Alignment = 2 EndProperty ItemType3 = 3 BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblAdresse" Object.Top = 1134 Object.Width = 3742 Object.Height = 225 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Adresse" Alignment = 2 EndProperty ItemType4 = 3 BeginProperty Item4 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblDatumtaeglich" Object.Left = 454 Object.Top = 2381 Object.Width = 900 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Datum" Alignment = 2 EndProperty ItemType5 = 3 BeginProperty Item5 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblLokal" Object.Left = 1474 Object.Top = 2381 Object.Width = 900 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Lokal" Alignment = 1 EndProperty ItemType6 = 3 BeginProperty Item6 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblTag" Object.Top = 2381 Object.Width = 405 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Tag" EndProperty ItemType7 = 3 BeginProperty Item7 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblAusserHaus" Object.Left = 2494 Object.Top = 2381 Object.Width = 1245 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Außer Haus" Alignment = 1 EndProperty ItemType8 = 5 BeginProperty Item8 {1C13A8E3-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Linie2" Object.Top = 2381 Object.Width = 3825 EndProperty ItemType9 = 3 BeginProperty Item9 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblMwSt19" Object.Left = 1587 Object.Top = 2608 Object.Width = 900 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "19% MwSt" Alignment = 1 EndProperty ItemType10 = 3 BeginProperty Item10 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblMwSt7" Object.Left = 2835 Object.Top = 2608 Object.Width = 795 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 8.25 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "7% MwSt" EndProperty ItemType11 = 5 BeginProperty Item11 {1C13A8E3-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Linie3" Object.Top = 2948 Object.Width = 3825 EndProperty EndProperty SectionCode1 = 4 BeginProperty Section1 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich1" Object.Height = 345 NumControls = 4 ItemType0 = 4 BeginProperty Item0 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtLokal" Object.Left = 1587 Object.Width = 900 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType1 = 4 BeginProperty Item1 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtDatum" Object.Left = 454 Object.Width = 950 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 2 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 0 Format = "" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty ItemType2 = 4 BeginProperty Item2 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtTag" Object.Width = 405 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 0 Format = "" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 0 EndProperty EndProperty ItemType3 = 4 BeginProperty Item3 {1C13A8E2-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "txtAusserHaus" Object.Left = 2494 Object.Width = 1245 Object.Height = 255 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty EndProperty SectionCode2 = 8 BeginProperty Section2 {1C13A8E0-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Bereich5" Object.Height = 1485 NumControls = 9 ItemType0 = 3 BeginProperty Item0 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblGesamtUmsatz" Object.Top = 1020 Object.Width = 1905 Object.Height = 330 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 11.25 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Gesamtumsatz" EndProperty ItemType1 = 5 BeginProperty Item1 {1C13A8E3-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "Linie1" Object.Top = 113 Object.Width = 3795 EndProperty ItemType2 = 3 BeginProperty Item2 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblNetto" Object.Top = 227 Object.Width = 1125 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "Netto" EndProperty ItemType3 = 3 BeginProperty Item3 {1C13A8E1-A0B6-11D0-848E-00A0C90DC8A9} _Version = 393216 Name = "lblMwSt" Object.Top = 454 Object.Width = 1155 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Object.Caption = "MwSt" EndProperty ItemType4 = 13 BeginProperty Item4 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionBrutto" Object.Left = 2494 Object.Top = 1020 Object.Width = 1245 Object.Height = 330 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 11.25 Charset = 0 Weight = 700 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType5 = 13 BeginProperty Item5 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionNettoAusserHaus" Object.Left = 2494 Object.Top = 227 Object.Width = 1245 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType6 = 13 BeginProperty Item6 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionMwStLokal" Object.Left = 1474 Object.Top = 454 Object.Width = 1005 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType7 = 13 BeginProperty Item7 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionNettoLokal" Object.Left = 1474 Object.Top = 227 Object.Width = 1005 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty ItemType8 = 13 BeginProperty Item8 {49FF6930-2B8C-11D1-8DA9-00A0C90FFFC2} _Version = 393216 Name = "FunktionMwStAusserHaus" Object.Left = 2494 Object.Top = 454 Object.Width = 1245 Object.Height = 240 BeginProperty Font {0BE35203-8F91-11CE-9DE3-00AA004BB851} Name = "Arial" Size = 9.75 Charset = 0 Weight = 400 Underline = 0 'False Italic = 0 'False Strikethrough = 0 'False EndProperty Alignment = 1 BeginProperty DataFormat {6D835690-900B-11D0-9484-00A0C91110ED} Type = 1 Format = "#.##0,00 ""€""" HaveTrueFalseNull= 0 FirstDayOfWeek = 0 FirstWeekOfYear = 0 LCID = 1031 SubFormatType = 2 EndProperty EndProperty EndProperty End Attribute VB_Name = "ReportUmsatzbericht" Attribute VB_GlobalNameSpace = False Attribute VB_Creatable = False Attribute VB_PredeclaredId = True Attribute VB_Exposed = False Sub BerichtUmsatz(VarDatum1, VarDatum2) Dim objConn As ADODB.Connection Dim rsUmsatz As ADODB.Recordset Dim strPath As String Set objConn = New ADODB.Connection Set rsUmsatz = New ADODB.Recordset On Error GoTo err_Handler strPath = App.Path If Right$(strPath, 1) <> "\" Then strPath = strPath & "\" With objConn .Provider = "Microsoft Jet 4.0 OLE DB Provider" .Properties("Jet OLEDB:Database Password") = VarPasswordDatenbank .ConnectionString = "Data Source=" & VarDatenbank .CursorLocation = adUseClient .Open End With 'Tabelle OffeneTische öffnen With rsUmsatz .ActiveConnection = objConn .CursorLocation = adUseClient .LockType = adLockOptimistic .Open "Select Datum,Format(Datum,'ddd')as Tag, Sum(Gesamt) AS Gesamt,Sum(Lokal) As SummeLokal, Sum(Lokal)/1.19 AS NettoLokal,(SummeLokal-NettoLokal) As SummeMwStLokal, Sum(AußerHaus) As SummeAusserHaus, Sum(AußerHaus)/1.07 As NettoAusserHaus, (SummeAusserHaus-NettoAusserHaus) As SummeMwStAusserHaus from AlleUmsaetze Where Datum >= #" & Format$(VarDatum1, "mm-dd-yyyy") & "# and Datum <= #" & Format$(VarDatum2, "mm-dd-yyyy") & "# Group by Datum" 'SQL = "SELECT * FROM Termine WHERE Datum = #" & Format$(Now, "m-d-yy") & "#" End With Set Me.DataSource = rsUmsatz Set Me.Sections("Bereich4").Controls("Symbol1").Picture = LoadPicture(App.Path & "\Logos\Logo_Lokal.emf") Me.Sections("Bereich4").Controls("lblAdresse").Caption = "Gutenbergstr. 20 - 67346 Speyer - Tel:74611" If VarDatum1 = VarDatum2 Then Me.Sections("Bereich4").Controls("lblDatum").Caption = VarDatum1 Else Me.Sections("Bereich4").Controls("lblDatum").Caption = "von " & VarDatum1 & " bis " & VarDatum2 End If Me.Sections("Bereich1").Controls("txtTag").DataField = rsUmsatz.Fields("Tag").Name Me.Sections("Bereich1").Controls("txtDatum").DataField = rsUmsatz.Fields("Datum").Name Me.Sections("Bereich1").Controls("txtLokal").DataField = rsUmsatz.Fields("SummeLokal").Name Me.Sections("Bereich1").Controls("txtAusserHaus").DataField = rsUmsatz.Fields("SummeAusserHaus").Name Me.Sections("Bereich5").Controls("lblNetto").Caption = "Netto" Me.Sections("Bereich5").Controls("FunktionNettoLokal").DataField = rsUmsatz.Fields("NettoLokal").Name Me.Sections("Bereich5").Controls("FunktionNettoAusserHaus").DataField = rsUmsatz.Fields("NettoAusserHaus").Name Me.Sections("Bereich5").Controls("lblMwSt").Caption = "MwSt" Me.Sections("Bereich5").Controls("FunktionMwStLokal").DataField = rsUmsatz.Fields("SummeMwStLokal").Name Me.Sections("Bereich5").Controls("FunktionMwStAusserHaus").DataField = rsUmsatz.Fields("SummeMwStAusserHaus").Name Me.Sections("Bereich5").Controls("FunktionBrutto").DataField = Format(rsUmsatz.Fields("Gesamt").Name, "Currency") Me.Show 1 'me.PrintReport Set objConn = Nothing Set rsUmsatz = Nothing exit_Sub: On Error GoTo 0 Set objConn = Nothing Set rsUmsatz = Nothing Exit Sub err_Handler: MsgBox "Fehlernummer " & Err.Number & Chr$(13) & Error$(Err), _ vbCritical, "Fehler" Resume exit_Sub Set objConn = Nothing Set rsUmsatz = Nothing End Sub Private Sub DataReport_Initialize() Call frmBerichte.BerichtePositionieren(ReportUmsatzbericht) End Sub