VBA ADO Summe aus Recordset bilden

  • Access

Es gibt 20 Antworten in diesem Thema. Der letzte Beitrag () ist von petaod.

    VBA ADO Summe aus Recordset bilden

    Guten Morgen zusammen.
    Ich bin neu hier bei VB-Paradise und freue mich auf gemeinsame Lösungsansätze, bidirektionale Hilfestellung und Erfahrungsaustausch.
    ---
    Ich mache derzeit einen Fernlehrgang zur VBA-Programmiererin und habe nun eine Denkblokade bei folgender Aufgabenstellung:
    In der angehangenen DB soll ich eine Prozedur erstellen, die zu einem einzugebenden Ferienhausnamen die Summe der Mietpreise aller Belegungen in einer Meldung ausgibt. Bei der Eingabe des Ferienhausnames sollen die Anfangsbuchstaben genügen. Gibt es mehrere Ferienhäuser mit dem eingegebenen Buchstaben, sollen diese absteigend nach Mietpreisnummer aufgelistet werden.

    Mein Code bisher wie folgt:

    Visual Basic-Quellcode

    1. Sub Summe_der_Mieteinnahmen_je_Ferienhaus()
    2. Dim rs As New ADODB.Recordset
    3. Dim strSql As String
    4. Dim strMeldung As String
    5. Dim strFerienhaus As String
    6. strFerienhaus = InputBox("Welches Ferienhaus bitte?")
    7. strSql = "SELECT * FROM Belegung WHERE Ferienhaus Like '" & strFerienhaus & "%" _
    8. & "'"
    9. rs.Open strSql, CurrentProject.Connection, _
    10. adOpenForwardOnly, adLockOptimistic
    11. With rs
    12. If Not .EOF Then
    13. While .EOF = False
    14. strMeldung = strMeldung & "Haus " & .Fields("Ferienhaus") _
    15. & " " & .Fields("Mietpreis") _
    16. & vbCrLf
    17. .MoveNext
    18. Wend
    19. Else
    20. strMeldung = "Es gibt kein Ferienhaus names " & strFerienhaus & ". " & "Bitte prüfen Sie ihre Eingabe!"
    21. End If
    22. End With
    23. rs.Close
    24. Set rs = Nothing
    25. MsgBox strMeldung, vbInformation
    26. End Sub


    Meine Denkblockade bezieht sich auf den ersten Punkt der Aufgabe: die Summe der Mietpreise aller Belegungen eines Ferienhauses.

    Ich würde mich über einen Wink mit dem Zaunpfahl "Wie gehe ich die Summenbildung an" sehr freuen. :)

    Viele Grüße
    Nicole

    CodeTags gesetzt ~VaporiZed
    Dateien
    • Ferienhaus.zip

      (80,72 kB, 91 mal heruntergeladen, zuletzt: )

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Im SQL gibt es eine Sum() Funktion... evtl. das mal anschauen.
    w3schools.com/sql/sql_count_avg_sum.asp
    "Gib einem Mann einen Fisch und du ernährst ihn für einen Tag. Lehre einen Mann zu fischen und du ernährst ihn für sein Leben."

    Wie debugge ich richtig? => Debuggen, Fehler finden und beseitigen
    Wie man VisualStudio nutzt? => VisualStudio richtig nutzen

    Visual Basic-Quellcode

    1. Sub Summe_der_Mieteinnahmen_je_Ferienhaus()
    2. Dim rs As New ADODB.Recordset
    3. Dim strSql As String
    4. Dim strMeldung As String
    5. Dim strFerienhaus As String
    6. strFerienhaus = InputBox("Welches Ferienhaus bitte?")
    7. If strFerienhaus="" Then Exit Sub
    8. strSql = "SELECT Ferienhaus,SUM(Mietpreis) AS Summe FROM Belegung WHERE Ferienhaus Like '" & strFerienhaus & "%' GROUP BY Ferienhaus ORDER BY SUM(Mietpreis) DESC"
    9. rs.Open strSql, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
    10. If rs.EOF Then
    11. strMeldung = "Es gibt kein Ferienhaus names " & strFerienhaus & ". " & "Bitte prüfen Sie ihre Eingabe!"
    12. Else
    13. Do While Not rs.EOF
    14. strMeldung = strMeldung & "Haus " & .Fields("Ferienhaus") & " " & .Fields("Mietpreis") & vbCrLf
    15. rs.MoveNext
    16. Wend
    17. End If
    18. rs.Close
    19. MsgBox strMeldung, vbInformation
    20. End Sub
    Ungetestet, könnte aber passen.

    Edit:
    [OffTopic]
    Lernt ihr in eurem VBA-Fernseminar tatsächlich noch mit ungarischer Notation zu arbeiten, also z.B. die Strings mit str-Prefix zu deklarieren?
    Das ist veraltet und stammt aus Zeiten, als man als IDE noch den Texteditor verwendete.
    In Microsoft-Code-Beispielen findet man so was nicht, dort wird Pascal-Notation verwendet.
    Nach den Namenskonventionen ist ungarische Notation in VBA und VB6 zwar nicht explizit verboten.
    Spätestens beim Nachfolger VB.Net jedoch schon. Dort ist zu lesen:
    Do not use Hungarian notation!
    Wenn du also einigermaßen zukunftsorientiert sein willst, mach es gleich richtig.
    [/OffTopic]
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „petaod“ ()

    Hallo petaod,

    genauso habe ich jetzt gelöst bekommen und deine Antwort grade erst gelesen ;-).

    Nun hab ich bei einer weiteren Aufgabe die Fehlermeldung:

    "Die SELECT-Anweisung schließt ein reserviertes Wort oder einen Argumentnamen ein, das/der falsch, mit falscher Zeichensetzung oder überhaupt nicht eingegeben wurde."

    Die Abfrage habe ich zuvor über den Abfragentwurf erstellt und da läuft es.

    Code wie folgt:

    Visual Basic-Quellcode

    1. 'Erstellen Sie eine Prozedur, mit der Sie - auf Basis der Belegungsliste - den Mieter ermitteln, durch den
    2. 'die höchsten Mieteinnahmen erzeilt werden.
    3. 'Stellen Sie gleichzeitig auch die Anzahl seiner Buchungen fest.
    4. 'Zeigen Sie Name, Vorname, Ort, Mieteinnahmen und Anzahl der Buchungen für diesen Mieter mit
    5. 'einer Meldung am Bidlschirm an.
    6. '.... die Aufgabe geht noch weiter....die Meldung soll dann noch in der Tabelle im Feld Bemerkung hintenangestellt werden.
    7. Sub Summe_der_Mieterermittlung_MaxMieteinnahmen()
    8. Dim rs As New ADODB.Recordset
    9. Dim strSql As String
    10. Dim strMeldung As String
    11. Dim aktName As String, aktVorname As String
    12. strSql = "SELECT TOP 1, Name, Vorname, Ort, Count(*) AS Anzahl, Format(Sum(Mietpreis), '###.00 €') AS SummeMieteDerBuchungen" & _
    13. "FROM Mieter INNER JOIN Belegung ON Mieter.MieterNr = Belegung.MieterNr" & _
    14. "GROUP BY Name, Vorname"
    15. rs.Open strSql, CurrentProject.Connection, _
    16. adOpenForwardOnly, adLockOptimistic
    17. With rs
    18. If Not .EOF Then
    19. While .EOF = False
    20. If .Fields("Name") = aktName And .Fields("Vorname") = aktVorname Then
    21. Else
    22. strMeldung = strMeldung & "Bester Mieter!" & vbCrLf & _
    23. "Mieteinnahmen: " & .Fields("SummeMieteDerBuchungen") & vbCrLf & _
    24. "Anzahl der Buchungen" & .Fields("Anahl")
    25. End If
    26. aktName = .Fields("Name")
    27. aktVorname = .Fields("Vorname")
    28. .MoveNext
    29. Wend
    30. ' Else
    31. ' strMeldung = "Keine Else-Meldung programmiert!"
    32. End If
    33. End With
    34. rs.Close
    35. Set rs = Nothing
    36. MsgBox strMeldung, vbInformation, ("Unser Champion: " & Vorname & " " & " aus: " & Ort)
    37. End Sub

    Diese Fehlermeldung macht mich wahnsinnig, da Sie mir keine Anhaltspunkt gibt, wo ich suchen soll, bzw. ich verstehe sie einfach nicht. Kannst du mir das erklären? Ich möchte keine direkte Lösung, i möchte es sehr gerne verstehen.

    Viele Grüße
    Nicole

    CodeTags gesetzt ~VaporiZed

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    1. Count(*) AS Anzahl, Format(Sum(Mietpreis), '###.00 €')? Ich hab zwar keine Ahnung von SQL, aber ich glaube nicht, dass das geht. Du vermischst SQL mit VB-Code.
    2. Bitte selbständig die korrekten CodeTags für Code verwenden. Ich hab's in Deinen Posts korrigiert.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.
    Vollzitat des direkten Vorposts an dieser Stelle entfernt ~VaporiZed

    Hallo,
    das Komma habe ich weggenommen, die Fehlermeldung bleibt. Das ist eine zur Verfügung gestellte Access-DB. Ich habe den SQL-Code über den Abfrageentwurf generiert, in VBA kopiert und die Zeichensetzung angepasst. Die Abfrage selbst läuft einwandfrei. Sie wir nur im VBA Editor als fehlerbehaftet ausgewiesen und dadurch startet mein Recordset nicht.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Ich habe meinen Quellcode jetzt wie folgt abgeändert:

    Visual Basic-Quellcode

    1. Sub Summe_der_Mieterermittlung_MaxMieteinnahmen()
    2. Dim rs As New ADODB.Recordset
    3. Dim strSql As String
    4. Dim strMeldung As String
    5. Dim aktName As String, aktVorname As String, aktOrt As String
    6. strSql = "SELECT Name, Vorname, Ort, Count(BelegNr) AS Anzahl, Format(SUM(Mietpreis), '###.00 €') AS SummeMieteDerBuchungen " & _
    7. "FROM Mieter INNER JOIN Belegung ON Mieter.MieterNr = Belegung.MieterNr " & _
    8. "GROUP BY Name, Vorname, Ort"
    9. rs.Open strSql, CurrentProject.Connection, _
    10. adOpenForwardOnly, adLockOptimistic
    11. With rs
    12. If Not .EOF Then
    13. While .EOF = False
    14. If .Fields("Name") = aktName And .Fields("Vorname") = aktVorname Then
    15. Else
    16. strMeldung = strMeldung & "Bester Mieter!" & vbCrLf & _
    17. "Mieteinnahmen: " & .Fields("SummeMieteDerBuchungen") & vbCrLf & _
    18. "Anzahl der Buchungen " & .Fields("Anzahl")
    19. End If
    20. aktName = .Fields("Name")
    21. aktVorname = .Fields("Vorname")
    22. aktOrt = .Fields("Ort")
    23. .MoveNext
    24. Wend
    25. ' Else
    26. ' strMeldung = "Keine Else-Meldung programmiert!"
    27. End If
    28. End With
    29. rs.Close
    30. Set rs = Nothing
    31. MsgBox strMeldung, vbInformation, ("Unser Champion: " & aktVorname & " " & aktName & " " & " aus: " & aktOrt)
    32. End Sub


    Nun gibt er mir in der MessageBox aber alle Summen der Mietpreise zu den einzelnen Mietern aus. Wie kann ich das auf dem Maximalwert, also auf den Mieter mit der höchsten Summe der Mieteinnahmen beschränken?

    LG

    CodeTags gesetzt ~VaporiZed

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Hi,

    ich würde das Format aus dem Query entfernen und lediglich bei einer Ausgabe anwenden:

    Visual Basic-Quellcode

    1. strSql = "SELECT Top 1 Name, Vorname, Ort, Count(BelegNr) AS Anzahl, SUM(Mietpreis) AS SummeMieteDerBuchungen " & _
    2. "FROM Mieter INNER JOIN Belegung ON Mieter.MieterNr = Belegung.MieterNr " & _
    3. "GROUP BY Name, Vorname, Ort" & _
    4. "ORDER BY SummeMieteDerBuchungen DESC"
    5. ..
    6. strMeldung = strMeldung & "Bester Mieter!" & vbCrLf & _
    7. "Mieteinnahmen: " & Format(.Fields("SummeMieteDerBuchungen"), "###.00 €") & vbCrLf & _
    8. "Anzahl der Buchungen " & .Fields("Anzahl")


    LG
    Das ist meine Signatur und sie wird wunderbar sein!
    Vollzitat des direkten Vorposts an dieser Stelle entfernt ~VaporiZed

    Mit dem Format das hab ich gemacht. Wenn ich aber die ORDER BY-Klausel anfüge, dann bekomme ich die Meldung: Für mindestens einen erforderlichen Parameter wurde kein Wert angegeben.

    LG

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Vollzitat des direkten Vorposts an dieser Stelle entfernt ~VaporiZed

    Blank hatte ich in meinem Code schon eingefügt, da liegt es nicht dran. Ich denke es liegt daran, da SummeMieteDerBuchungen im SQL-Statement ja noch leer ist!?

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „VaporiZed“ ()

    Moderatorenanweisung @Nicole2503:
    1. Keine Vollzitate der direkten Vorposts (§ 4 Abs. 3f der Boardregeln)
    2. selbständig korrekte CodeTags verwenden - nochmal schreib ich das nicht.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.
    Hallo Nicole,

    du schreibst das eine -Access- Datenbank verwendet wird, du verwendest ein in Access reserviertes wort
    und zwar Name !

    entweder du änderst das zu mName oder xName oder setzte es mal in Klammern [Name]

    VB.NET-Quellcode

    1. strSql = "SELECT Top 1 [Name], Vorname, Ort, Count(BelegNr) AS Anzahl,


    hier Link zum lesen
    support.microsoft.com/de-de/of…55-4b12-91a9-f855bdd9c5a2

    gruss
    kasi
    Das mit [Name] könnte sein.
    Und noch ein Ansatz:
    Vielleicht kommst du mit

    SQL-Abfrage

    1. ORDER BY SUM(Mietpreis) DESC

    zum Erfolg.

    Ansonsten würde ich einfach mal ein wenig testen innerhalb einer Access-Query.
    Was dort zum Ziel führt, sollte auch in VBA anwendbar sein.
    --
    If Not Program.isWorking Then Code.Debug Else Code.DoNotTouch
    --
    das hier sieht auch komisch aus

    VB.NET-Quellcode

    1. rs.Open strSql, [color=#0000CD]CurrentProject[/color].Connection, _
    2. adOpenForwardOnly, adLockOptimistic


    passe das mal an, und sage ob Daten zurück kommen

    VB.NET-Quellcode

    1. Dim oRS As New ADODB.Recordset
    2. Dim fld As ADODB.Field
    3. ' Recordset-Objekt erstellen
    4. With oRS
    5. .CursorLocation = adUseClient
    6. .Open "SELECT * FROM Belegung", adoConnection, adOpenStatic, adLockReadOnly
    7. Do Until oRS.EOF
    8. For Each fld In oRS.Fields
    9. Debug.Print fld.Value & ";";
    10. Next
    11. Debug.Print
    12. oRS.MoveNext
    13. Loop
    14. oRS.Close
    15. End With


    füge noch ein Order By hinzu

    VB.NET-Quellcode

    1. strSql = "SELECT Name, Vorname, Ort, Count(BelegNr) AS Anzahl, Format(SUM(Mietpreis), '###.00 €') AS SummeMieteDerBuchungen " & _
    2. "FROM Mieter INNER JOIN Belegung ON Mieter.MieterNr = Belegung.MieterNr " & _
    3. "GROUP BY Name, Vorname, Ort " & _
    4. "ORDER BY Count(Mieter.MieterNr) DESC;"

    Dieser Beitrag wurde bereits 3 mal editiert, zuletzt von „Kasi“ ()

    Hallo zusammen,

    mein SQL-Statement und mein Recordset funktionieren nun. Jetzt muss ich die Lösung dahingehend erweitern, dass in dem gefundenen Datensatz das Feld Bemerkung aktualisiert wird.

    Das habe ich wie folgt eingebaut:

    Visual Basic-Quellcode

    1. Sub Summe_der_Mieterermittlung_MaxMieteinnahmen()
    2. Dim rs As New ADODB.Recordset
    3. Dim rs_2 As New ADODB.Recordset
    4. Dim strSql As String
    5. Dim strMeldung As String
    6. Dim aktName As String, aktVorname As String, aktOrt As String, aktBemerkung As String
    7. strSql = "SELECT TOP 1, Name, Vorname, Ort, Bemerkung, Count(BelegNr) AS Anzahl, Format(Sum(Mietpreis), '###.00 €') AS SummeMieteDerBuchungen " & _
    8. " FROM Mieter INNER JOIN Belegung ON Mieter.MieterNr = Belegung.MieterNr" & _
    9. " GROUP BY Name, Vorname, Ort, Bemerkung"
    10. rs.Open strSql, CurrentProject.Connection, _
    11. adOpenForwardOnly, adLockOptimistic
    12. With rs
    13. If Not .EOF Then
    14. While .EOF = False
    15. If .Fields("Name") = aktName And .Fields("Vorname") = aktVorname Then
    16. Else
    17. strMeldung = strMeldung & "Bester Mieter!" & vbCrLf & _
    18. "Mieteinnahmen: " & Format(.Fields("SummeMieteDerBuchungen"), "###.00 €") & vbCrLf & _
    19. "Anzahl der Buchungen " & .Fields("Anzahl")
    20. aktName = .Fields("Name")
    21. aktVorname = .Fields("Vorname")
    22. aktOrt = .Fields("Ort")
    23. If .Fields("Bemerkung") <> "" Then
    24. aktBemerkung = .Fields("Bemerkung") & vbCrLf & strMeldung
    25. Else
    26. .Fields("Bemerkung") = strMeldung
    27. End If
    28. .MoveNext
    29. End If
    30. Wend
    31. ' Else
    32. ' strMeldung = "Keine Else-Meldung programmiert!"
    33. End If
    34. End With
    35. rs.Close
    36. Set rs = Nothing
    37. MsgBox strMeldung, vbInformation, ("Unser Champion: " & aktVorname & " " & aktName & " " & " aus: " & aktOrt)
    38. End Sub



    Im Lokalfenster wird mir meine aktBemerkung auch korrekt angezeigt, jedoch wird das Feld in der Tabelle nicht aktualisiert.

    Hat jemand eine Idee?

    Liebe Grüße

    @VaporiZed: Hier im Nachrichtenfenster habe ch die Code Tags jetzt drin. Sobald ich auf Absenden klicke sind sie weg. Was mae ich falsch?
    In meinem VBA-Editor waren sie auch von Anfang an drin. Entschuldigung.

    Dieser Beitrag wurde bereits 1 mal editiert, zuletzt von „Nicole2503“ ()

    Wahrscheinlich verstehst Du unter CodeTags was anderes als ich. Denn im VBA-Editor gibt es keine CodeTags. Was Du wohl meinst, ist IDE-internes Syntaxhighlighting. CodeTags sind eine foreninterne Sache.
    Man fügt seinen Code ein, markiert ihn und klickt auf [VB6.0] in der Editormenüleiste.
    Dadurch wird dem eigenen Text nur folgender Text hinzugefügt:
    Am Anfang der Markierung erscheint ein [vb] und am Ende der Markierung ein [/vb]. So wird durch Markieren und Anklicken aus Sub Foo ein [vb]Sub Foo[/vb].

    Und so wird darstellungstechnisch aus Fließtext

    Sub Foo

    ein

    Visual Basic-Quellcode

    1. Sub Foo


    Kannst Du gleich mal mit Deinem Post#19 ausprobieren, am besten dazu auf [Bearbeiten] klicken, dann [Erweiterte Bearbeitung], dann die Änderungen vornehmen und dann auf [Vorschau] klicken, bevor Du auf [Absenden] klickst.
    Dieser Beitrag wurde bereits 5 mal editiert, zuletzt von „VaporiZed“, mal wieder aus Grammatikgründen.

    Aufgrund spontaner Selbsteintrübung sind all meine Glaskugeln beim Hersteller. Lasst mich daher bitte nicht den Spekulatiusbackmodus wechseln.