VBA - Tabelle öffnen und durchlaufen

Sibylle · 12. November 2019 um 06:46

Hai, Auskenner,

offensichtlich bin ich zu dumm, um mittels VBA eine Tabelle zu öffnen und dann die Datensätze zu durchlaufen - ich verstehe nur nicht, warum…
…wenn mich mal jemand in dem Punkt aufklären könnte!?
Weil ich den Provisionssatz an allen möglichen Stellen brauche, habe ich mr ein extra Modul gemacht; die zugrundeliegenden Tabellen sind verknüpft.

Hier der Code, der mir in der Zeile „set rs…“ als Fehler „Typen unverträglich“ wirft (auch die expliziete Auswahl auf ADO oder DAO ändert nichts):

Option Explicit

Public Function ProvisionsSatzErmitteln(suchTyp As String, suchDatum As Date, fuerSumme As Single) As Single
On Error GoTo Err\_ProvisionsSatzErmitteln

 Dim rs As Recordset
 Dim strSQL As String
 Dim ergebnis As Single
 Dim Datum As String

 ' Datum in US-Format umwandeln
 Datum = Month(suchDatum) & "/" & Day(suchDatum) & "/" & Year(suchDatum)
 strSQL = "SELECT \* FROM ProvisionsSaetze;"
 Set rs = CurrentDb.OpenRecordset(strSQL)
 Do Until ((rs("abSumme") \> fuerSumme) Or rs.EOF)
 ergebnis = rs(3)
 If Not rs.EOF Then
 rs.MoveNext
 Else
 Exit Do
 End If
 Loop
 rs.Close
 Set rs = Nothing
 ProvisionsSatzErmitteln = ergebnis

Exit\_ProvisionsSatzErmitteln:
 Exit Function

Err\_ProvisionsSatzErmitteln:
 MsgBox Err.Description
 Resume Exit\_ProvisionsSatzErmitteln

End Function

Wenn ich „Option explicit“ und „DIM rs AS…“ rauswerfe, dann kommt er in die Schleife (dimensioniert rs offenbar automatisch als recordset), rs.EOF wird aber niemals true, was dazu führt, daß er aus der Tabelle rutscht…

Kann mir (bitte, bitte, bitte) jemand helfen?

Gruß
Sibylle

df6gl · 12. November 2019 um 06:46

Hallo,

offensichtlich bin ich zu dumm, um mittels VBA eine Tabelle zu
öffnen und dann die Datensätze zu durchlaufen - ich verstehe
nur nicht, warum…
…wenn mich mal jemand in dem Punkt aufklären könnte!?

dazu kann ich Dich nicht aufklären

Weil ich den Provisionssatz an allen möglichen Stellen
brauche, habe ich mr ein extra Modul gemacht; die
zugrundeliegenden Tabellen sind verknüpft.

Erklär mal den Algorithmus der Berechnung des Provisionssatzes

Hier der Code, der mir in der Zeile „set rs…“ als Fehler
„Typen unverträglich“ wirft (auch die expliziete Auswahl auf
ADO oder DAO ändert nichts):

Option Explicit
Public Function ProvisionsSatzErmitteln(suchTyp As String, suchDatum As Date, fuerSumme As Double) As Double 'unbedingt Double oder Currency benutzen
On Error GoTo Err\_ProvisionsSatzErmitteln
Dim rs As DAO.Recordset 'hier DAO.Deklaration
Dim strSQL As String
Dim ergebnis As Currency ' dito
Dim Datum As String

' Datum in US-Format umwandeln
Datum = Month(suchDatum) & "/" & Day(suchDatum) & "/" &  


> Year(suchDatum)

 'das ist kein US-Datum, die # fehlen

' besser:
Datum = format(suchDatum,"\#mm/dd/yyyy\#")

'ich bevorzuge das ISO-Datum:
Datum = format(suchDatum,"\#yyyy-mm-dd\#")

'für was brauchst Du denn dieses Datum überhaupt? 
 
strSQL = "SELECT \* FROM ProvisionsSaetze order by absumme" ' statt Sternchen besser die Feldliste nehmen...und sortieren!
 
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpensnapshot) ' geht schneller, weil nicht editierbar 
 
Do Until ((rs("abSumme") \> fuerSumme) Or rs.EOF) ' Sortierung des "absumme"-Feldes nötig, wie oben gezeigt
ergebnis = rs(3) ' 4.Feld ist was ?
if Not rs.EOF Then ' überflüssig
rs.MoveNext
Else  
Exit Do  
End If ' überflüssig , unsinnig
Loop


'vermutlich haut's die Schleife kaputt, wenn kein DS vorhanden ist. Deshalb besser so:


    
    .
    .
    .
    Do Until rs.EOF
    if rs("abSumme") \> fuerSumme then 
    ergebnis = rs(3) 
    Exit Do
    End If 
    
    rs.MoveNext
    Loop
    .
    .








rs.Close 'das ist sehr gut
Set rs = Nothing 'dito :smile:

ProvisionsSatzErmitteln = ergebnis
 
Exit\_ProvisionsSatzErmitteln:
Exit Function
 
Err\_ProvisionsSatzErmitteln:
MsgBox Err.Description
Resume Exit\_ProvisionsSatzErmitteln
 
End Function

vermutlich lässt sich das GAnze mit einem Einzeiler schreiben:

ProvisionsSatzErmitteln =Currentdb.Openrecordset("SELECT Provisionssatz FROM ProvisionsSaetze where [abSumme] \> " & fuerSumme & " order by absumme", dbOpenShnapshot)(0)

Sollte es weiterhin Typenunverträglichkeit geben, schau in den Verweisen nach, ab die DAO3.6-Library angehakt ist.

Gruß
Franz, DF6GL

Sibylle · 12. November 2019 um 06:47

Hai, Franz,

vorneweg: der Verweis auf die DAO-Library war’s offensichtlich

Erklär mal den Algorithmus der Berechnung des Provisionssatzes

Algorithmus ist gut - das ist „gewachsen“…
Veranstaltungsverkäufer-Provisionen.
Es gibt unterschiedliche Veranstaltungstypen mit jeweils eigenen Provisionssätzen und Staffelstufen. Welche Stufe herangezogen wird, ist von der erzielten Summe über alle Veranstaltungen eines Tages abhängig.
Das ist es im Prinzip…
…nur daß es da noch „Schallmauern“ bei einigen Veranstaltungen gibt, deren Überschreitung für alle Veranstaltungen des Tages die nächsthöhere Staffelstufe für die Provisionsberechnung bedeutet…
…und Kundengruppen, die einen prozentualen Ab- oder Aufschlag auf den Provisionssatz bei ihren eigenen Bestellungen/die ganze Veranstaltung/den ganzen Tag bedeuten (das kommt bisher darauf an, wer die Provisionsberechnung bearbeitet - aber „man denkt da mal drüber nach“…)…
…und daß sich die Provisionssätze alle naselang ändern, die endgültigen Summen aber auch mal erst ein Jahr später feststehen…

Aber ich hab da noch ein paar Details:

suchDatum As Date, fuerSumme As Double) As Double 'unbedingt
Double oder Currency benutzen
Dim ergebnis As Currency ’ dito

wieso?
Ich will doch Prozentwerte übergeben!?

’ Datum in US-Format umwandeln
Datum = Month(suchDatum) & „/“ & Day(suchDatum) & „/“ &

Year(suchDatum)

'das ist kein US-Datum, die # fehlen

die sind im (testhalber rausgeschmissenen) SQL-String eingebaut - das war quick-and-dirty, weil ich diesen Typen-Fehler hatte

Datum = format(suchDatum,"#mm/dd/yyyy#")

so stand’s da mal und tut’s auch wieder

'ich bevorzuge das ISO-Datum:
Datum = format(suchDatum,"#yyyy-mm-dd#")

die Access-Hilfe sagte „US-Datum“…

statt Sternchen besser die Feldliste nehmen…und sortieren!

der komplette SQL-String ist unwesentlich länger - siehe „Algorithmus“

ergebnis = rs(3) ’ 4.Feld ist was ?

ein Prozent-Wert

if Not rs.EOF Then ’ überflüssig
rs.MoveNext
Else
Exit Do
End If ’ überflüssig , unsinnig

pure Verzweiflung…

'vermutlich haut’s die Schleife kaputt, wenn kein DS vorhanden
ist.

Ja, eben…

Deshalb besser so:
Do Until rs.EOF
if rs(„abSumme“) > fuerSumme then
ergebnis = rs(3)
Exit Do
End If

rs.MoveNext
Loop

Die if-Bedingung brauche ich anders (hab ich), aber ich verstehe nicht, warum ich das extra abfragen muß und nicht in der Do-Bedingung mit OR unterbringen kann…?

rs.Close 'das ist sehr gut
Set rs = Nothing 'dito

oh - danke…
das hat mir ein tobender Server-Admin auf alle Zeiten eingebleut… *ggg*

vermutlich lässt sich das GAnze mit einem Einzeiler schreiben:

ProvisionsSatzErmitteln =Currentdb.Openrecordset(„SELECT
Provisionssatz FROM ProvisionsSaetze where [abSumme] > " &
fuerSumme & " order by absumme“, dbOpenShnapshot)(0)

So nicht…
a) bräuchte ich "MAX von [absumme] WHERE ([abSumme]

Netwolf · 12. November 2019 um 06:47

Moin Moin,

ich verstehe nur nicht, warum…

tja, da sind wir schon mal zwei, die den Sinn der Aktion nicht verstehen

…wenn mich mal jemand in dem Punkt aufklären könnte!?

schau dir mal deinen Code an, was passiert, mal abgesehen vom falschen Datumsformat?

Public Function ProvisionsSatzErmitteln(suchTyp As String,
suchDatum As Date, fuerSumme As Single) As Single
On Error GoTo Err_ProvisionsSatzErmitteln

Du definierst drei Parameter für eine Auswahl:

suchtyp (was soll das werden, wenn es fertig ist?)
suchdatum as date (ok, wofür auch immer das nötig ist)
fuersumme (ok, wofür auch immer das nötig ist)

Dim rs As Recordset

ok

Dim strSQL As String

ok

Dim ergebnis As Single

nö, nicht nötig, aber von mir aus

Dim Datum As String

ne ne ne, du solltest es tunlichst vermeiden reservierte Worte in deinem Code als eigene Variablen zu nutzen. Z.B. Datum und Name …

’ Datum in US-Format umwandeln
Datum = Month(suchDatum) & „/“ & Day(suchDatum) & „/“ &
Year(suchDatum)

ok, da haben wir das erste Kriterium falsch definiert
aber warum machen wir das alles? es wir ja hinterher gar nicht genutzt?

strSQL = „SELECT * FROM ProvisionsSaetze;“

schön, gib mir alles

Set rs = CurrentDb.OpenRecordset(strSQL)

schön, gib mir immer noch alles, unsortiert, ohne Kriterien

Do Until ((rs(„abSumme“) > fuerSumme) Or rs.EOF)

aha, das zweite Kriterium kommt „vielleicht“ zum Einsatz, wenn min. der erste Datensatz dem Kriterium entspricht

ergebnis = rs(3)

dann wird der Wert??? des dritten Feldes als „ergebnis“ (imho auch ein reserviertes Wort) gespeichert.

If Not rs.EOF Then
rs.MoveNext

wenn deine Tabelle noch weitere Datensätze gem. Kriterium rs(„abSumme“) > fuerSumme enthält, gehe zum nächsten Datensatz und speichere den Wert wieder in „ergebnis“. Addiert oder summiert wird hier nichts

ProvisionsSatzErmitteln = ergebnis

dein "ergebnis ist der letzte gefundene Wert in der Tabelle für das Kriterium: rs(„abSumme“) > fuerSumme
das kann nicht richtig sein, oder?

Wie schon Franz richtig schrieb: eine Zeile Code sollte deinen Wunsch erfüllen.

Nutze dlookup um dein Ergebnis zu bekommen:
ProvisionsSatz = dlookup(„FeldNr3“,„ProvisionsSaetze“,"[abSumme] > fuerSumme and datum was??? and suchtyp = was???")

Lese gerade deine Antwort…zum Posting von Franz, also dürfte Dmax wohl eher für dich in Frage kommen!?

Grüße aus Rostock
Wolfgang
(Netwolf)

df6gl · 12. November 2019 um 06:47

Hallo Sibylle

vorneweg: der Verweis auf die DAO-Library war’s offensichtlich

ist oft so…

Erklär mal den Algorithmus der Berechnung des Provisionssatzes

Algorithmus ist gut - das ist „gewachsen“…
Veranstaltungsverkäufer-Provisionen.
Es gibt unterschiedliche Veranstaltungstypen mit jeweils
eigenen Provisionssätzen und Staffelstufen. Welche Stufe
herangezogen wird, ist von der erzielten Summe über alle
Veranstaltungen eines Tages abhängig.
Das ist es im Prinzip…
…nur daß es da noch „Schallmauern“ bei einigen
Veranstaltungen gibt, deren Überschreitung für alle
Veranstaltungen des Tages die nächsthöhere Staffelstufe für
die Provisionsberechnung bedeutet…
…und Kundengruppen, die einen prozentualen Ab- oder
Aufschlag auf den Provisionssatz bei ihren eigenen
Bestellungen/die ganze Veranstaltung/den ganzen Tag bedeuten
(das kommt bisher darauf an, wer die Provisionsberechnung
bearbeitet - aber „man denkt da mal drüber nach“…)…
…und daß sich die Provisionssätze alle naselang ändern, die
endgültigen Summen aber auch mal erst ein Jahr später
feststehen…

ok, versteh ich nicht wirklich ;-() Geeigneter wäre, den Tabellenaufabu zu beschreiben…

Aber ich hab da noch ein paar Details:

suchDatum As Date, fuerSumme As Double) As Double 'unbedingt
Double oder Currency benutzen
Dim ergebnis As Currency ’ dito

wieso?
Ich will doch Prozentwerte übergeben!?

Was soll denn „Prozentwerte“ heißen?

Real-Zahlen von 0,00 bis 100,00 ?

Die mußt Du auch berechnen und dabei bedenken, dass dadurch Summierungen und Divisionen entstehen. Der Datentyp „Single“ hat eine Genauigkeit von insgesamt 7 Stellen, d.h. man kann
0,000001 oder 99999,99 € z. B. GENAU darstellen und damit rechnen.

Wenn es aber ein paar Summierungen und Divisionen gibt, nehmen Rundungsfehler rapide zu und hinterher wundert man sich, wo denn nur der eine Cent geblieben ist

Double (Gleitkommazahl) hat eine Genauigkeit von 15 Stellen, was für viele Berechnungen ausreichend sein sollte.
Currency (Währung, Festkommazahl) hat 8 Byte Länge mit 4 Stellen Genauigkeit hinter dem Komma.

’ Datum in US-Format umwandeln
Datum = Month(suchDatum) & „/“ & Day(suchDatum) & „/“ &

Year(suchDatum)

'das ist kein US-Datum, die # fehlen

die sind im (testhalber rausgeschmissenen) SQL-String
eingebaut - das war quick-and-dirty, weil ich diesen
Typen-Fehler hatte

Datum = format(suchDatum,"#mm/dd/yyyy#")

so stand’s da mal und tut’s auch wieder

'ich bevorzuge das ISO-Datum:
Datum = format(suchDatum,"#yyyy-mm-dd#")

die Access-Hilfe sagte „US-Datum“…

ja, die verschweigt halt das ISO-Format, US- geht ja auch, lediglich ich bevorzuge ISO…

statt Sternchen besser die Feldliste nehmen…und sortieren!

der komplette SQL-String ist unwesentlich länger - siehe
„Algorithmus“

es braucht nicht die komplette Feldliste sein, es reichen die Felder, die für Berechnungen benötigt werden. Selbst die Felder für die Where-Condition brauchen nicht in der Select-Liste aufgeführt zu sein.

ergebnis = rs(3) ’ 4.Feld ist was ?

ein Prozent-Wert

naja, offensichtlich soll es ein Feld mit Datentyp Single sein , das ist klar, aber welche Bedeutung und welchen Namen hat das Feld? In der Select-Liste ist ja keine Reihenfolge vorgegeben, so dass es sich um das 4. Feld in der Ordinal-(Reihenfolge) der Tabelle handelt.

if Not rs.EOF Then ’ überflüssig
rs.MoveNext
Else
Exit Do
End If ’ überflüssig , unsinnig

pure Verzweiflung…

naja…

'vermutlich haut’s die Schleife kaputt, wenn kein DS vorhanden
ist.

Ja, eben…

Deshalb besser so:
Do Until rs.EOF
if rs(„abSumme“) > fuerSumme then
ergebnis = rs(3)
Exit Do
End If

rs.MoveNext
Loop

Die if-Bedingung brauche ich anders (hab ich), aber ich
verstehe nicht, warum ich das extra abfragen muß und nicht in
der Do-Bedingung mit OR unterbringen kann…?

weil dann in der DO-Bedingung ein Fehler entsteht. Es wird ja beim Ausführen des Statements gleichzeitig die Bedingung und auf rs.EOF geprüft. Bei nur rs.EOF wird ERST gecheckt, ob DS vorhanden sind, DANN werden vorhandene Werte geprüft.

rs.Close 'das ist sehr gut
Set rs = Nothing 'dito

oh - danke…
das hat mir ein tobender Server-Admin auf alle Zeiten
eingebleut… *ggg*

Da hat er was Gutes getan…

vermutlich lässt sich das GAnze mit einem Einzeiler schreiben:

ProvisionsSatzErmitteln =Currentdb.Openrecordset(„SELECT
Provisionssatz FROM ProvisionsSaetze where [abSumme] > " &
fuerSumme & " order by absumme“, dbOpenShnapshot)(0)

So nicht…

a) bräuchte ich "MAX von [absumme] WHERE ([abSumme] " &

fuerSumme & " order by absumme", dbOpenShnapshot)(0)

und
b) gibt’s da noch die neckischen Anpassungen der Staffelstufen

die sind sicher auch als SQl-String zu schreiben. Ansonsten kannst Du eine vorab erstellte und gespeicherte Abfrage mit irgendwelchen Berechnungen/Gruppierung auch in einem SQL-String benutzen:

Select irgendwas from abf_Abfrage where IrgendeinFeld=Irgendwas

Alles in allem danke ich Dir vielmals

Bitte sehr…

und viel Erfolg.

Gruß
Franz, DF6GL

Sibylle · 12. November 2019 um 06:47

Alles ist gut…
Hai, Netwolf,

auch Dir danke ich für die Mühe, aber der Code-Schnipsel ist das, was übrig blieb, nachdem ich versucht hab, herauszubekommen, was überhaupt falsch läuft und ich dann das Meiste von dem rausgeschmissen hab, was nicht mit dem Fehler zusammenhängen konnte…

Aber ich hab meine Lösung ja und der Code macht jetzt auch, was ich will

Gruß
Sibylle

Sibylle · 12. November 2019 um 06:47

Hai,

ok, versteh ich nicht wirklich ;-() Geeigneter wäre, den
Tabellenaufabu zu beschreiben…

Wie gesagt: gewachsen…
und der Tabellenaufbau, der mir hier vorliegt, ist auch „gewachsen“ - das würde hier den Rahmen sprengen

Kurz gesagt: würde ich den anzuwendenden Provisionssatz mit allen Varianten und Ausnahmen in einer Abfrage ermitteln, würde die sich über fast alle Tabellen ziehen.

Real-Zahlen von 0,00 bis 100,00 ?

.
.

Currency (Währung, Festkommazahl) hat 8 Byte Länge mit 4
Stellen Genauigkeit hinter dem Komma.

OK - hast’n Argument

es braucht nicht die komplette Feldliste sein, es reichen die
Felder, die für Berechnungen benötigt werden. Selbst die
Felder für die Where-Condition brauchen nicht in der
Select-Liste aufgeführt zu sein.

ist klar - ich wollte eigentlich nur sagen, daß ich doch einen ordentlichen SQL-String habe und hier übersichtshalber nur 'n nackichtes Statement reingestellt habe - und die schreib ich mit Indizes und nicht mit Feldnamen (die waren auch nur für hier)

weil dann in der DO-Bedingung ein Fehler entsteht. Es wird ja
beim Ausführen des Statements gleichzeitig die Bedingung und
auf rs.EOF geprüft. Bei nur rs.EOF wird ERST gecheckt, ob DS
vorhanden sind, DANN werden vorhandene Werte geprüft.

*Hand-vor-Stirn-klatsch*

b) gibt’s da noch die neckischen Anpassungen der Staffelstufen

Select irgendwas from abf_Abfrage where
IrgendeinFeld=Irgendwas

ist es schon

und viel Erfolg.

Danke, hab ich - dank Deines Schubses

Gruß
Sibylle