SQL-Abfrage im VBA-Code geht leider nicht

[tragger]

Hallo Leute,

ich habe mal wieder ein Problem. Ich habe eine Funktion, die bei Aufruf ein Datum zurückgibt:

Code [Auswählen] Erweitern

Public Function AblaufVersicherung(Start As Date, Laufzeit As String) As Date
   Dim Monat As String
   Dim Tag As String
   Dim Jahr As String
   Dim Startgerechnet As Date
   
   Startgerechnet = Start - 1
   Jahr = DatePart("yyyy", Startgerechnet)
   If Laufzeit = "1" Then
       Jahr = Jahr + 1
       AblaufVersicherung = "30.04." & Jahr
   ElseIf Laufzeit = "0,5" Then
       Monat = DatePart("m", Start)
       Tag = DatePart("d", Start)
       If Monat >= 5 And Monat <= 10 Then
           AblaufVersicherung = "31.10." & Jahr
       ElseIf Monat = 1 And Tag = 1 Then
           Jahr = Jahr + 1
           AblaufVersicherung = "30.04." & Jahr
       ElseIf Monat > 10 Then
           Jahr = Jahr + 1
           AblaufVersicherung = "30.04." & Jahr
       ElseIf Monat < 5 Then
           AblaufVersicherung = "30.04." & Jahr
       Else
           AblaufVersicherung = ""
       End If
   Else
       AblaufVersicherung = ""
   End If
End Function

Nun möchte ich bei Klick auf einen Button meine Datensätze in Abhängigkeit des Ablaufdatums sortieren lassen.

Mein Ansatz sieht folgendermaßen aus:

Code [Auswählen] Erweitern

Private Sub cmd_Aktuelle_Versicherung_Click()
   Dim sSQL$
   Dim Datum_Heute As Date
   
   Datum_Heute = Date

   sSQL$ = " SELECT * FROM qryVersicherung" _
       & " WHERE Datum_Heute < " & AblaufVersicherung(Me.gültig_ab, Me.Versicherungsdauer) _
       & " ORDER BY VersicherungsNr;"
   
   Me.RecordSource = sSQL
End Sub

Ich habe bereits per IsDate überprüft, ob die Funktion oben ein Datum liefert und eine wahre Antwort erhalten.

Liegt das Problem evtl. daran, dass SQL nur das amerikanische Datumsformat versteht, oder was muss ich ändern??

Fehlermeldu8ng:

Laufzeitfehler 3075

Syntaxfehler in Zahl in Abfrageausdruck 'Datum_Heute < 30.04.201'.

Verstehe das nicht, alleine wird mir ein genaues DAtum von der Funktion zurückgegeben, z.B. hier 30.04.2014

[database]

Hallo,

zum Einen muss das Datum entweder im amerikanischen oder im ISO - Format verwendet werden, zum Anderen
kann das

Code [Auswählen] Erweitern

WHERE Datum_Heute < " & AblaufVersicherung(Me.gültig_ab, Me.Versicherungsdauer) m.E. nichts liefern!

Der Vergleich in der WHERE-Klausel muss sich auf ein aktuelles Feld deiner Datenherkunft beziehen. Du vergleichst aber im SQL-String, der sich auf eine Abfrage als Datenquelle bezieht, das aktuelle Tagesdatum mit dem Rückgabewert einer Funktion, die beide offensichtlich nichts mit den Abfrageergebniswerten zu tun haben...

Ein Verschieben von Datum_Heute oder des Funktionsaufrufes in die Spaltenliste der Abfrage bringt allerdings auch nichts.
Somit musst du aus der qryVersicherung ein Feld für den Vergleich beziehen, entweder eines, dass du mit dem aktuellen Datum (Datum_Heute) oder eines, dass du mit deinem Funktionsrückgabewert vergleichen kannst.

Das aktuelle Tagesdatum musst du nicht zuvor in eine Variable quetschen, das kannst du auch direkt im SQL-String eintragen ...

Code [Auswählen] Erweitern

Format(Date(),"\#yyyy\-mm\-dd\#")
oder wenn du das über Variablen lösen willst:

Code [Auswählen] Erweitern


   Dim Datum_Heute As String
   Datum_Heute = Format(Date(),"\#yyyy\-mm\-dd\#")


Als letztes Argument darf ich noch anmerken, dass der Code einen Fehler generieren wird, wenn die Bedingungen deiner Funktion so gelagert sind, dass ein Leerstring ( "" ) zuückgegeben wird.
Ich würde da empfehlen, die Funktion nicht innerhalb der SQL-Geschichte auzuführen sondern zuvor und nur dann den SQL-String zu bilden und zuzuweisen, wenn ein realer Wert zurückgeliefert wird.

Schöne Grüße

EDIT:

Vorschlag verlege das Ablaufdatum in die qryVersicherung und sortiere dann nach diesem Feld ... ORDER BY Ablaufdatum ... ---> wenn du in der qry die Funktion AS Ablaufdatum eingebaut hast.

[tragger]

OK,

Also leider sind meine Kenntnisse bisher noch begrenzt.
Ich habe diese Funktion Ablaufdatum in einem Formular eingebaut, da ich meine Felder durch ein Formular fülle und dort den ermittelten Wert in einem Textfeld zurückgebe.

Kann ich in einer Abfrage einfach ein Feld erzeugen, dass in keiner Tabelle vorkommt?, wenn ja, wie lasse ich das füllen, wo muss die Funktion Ablauf stehen und an welches Ereignis muss diese gebunden werden?

Der leere String kann durch Eingaberestriktionen nicht eintreten, bzw. ist an der anderen Stelle dazu da, damit im Textfeld im leeren Formular nichts angezeigt wird.

[DF6GL]

Hallo,

so sollte es funktionieren:



Public Function AblaufVersicherung(Start As Date, Laufzeit As String) As String   'Laufzeit als String???
    Dim Monat As String
    Dim Tag As String
    Dim Jahr As String
    Dim Startgerechnet As Date
    Startgerechnet = Start - 1
    Jahr = DatePart("yyyy", Startgerechnet)
    If Laufzeit = "1" Then
        Jahr = Jahr + 1
        AblaufVersicherung =  "#04/30/" & Jahr & "#"
       ElseIf Laufzeit = "0,5" Then
        Monat = DatePart("m", Start)
        Tag = DatePart("d", Start)
        If Monat >= 5 And Monat <= 10 Then
            AblaufVersicherung = "#04/30/" & Jahr & "#"
        ElseIf Monat = 1 And Tag = 1 Then
            Jahr = Jahr + 1
            AblaufVersicherung = "#04/30/" & Jahr & "#"

        ElseIf Monat > 10 Then
            Jahr = Jahr + 1
            AblaufVersicherung = "#04/30/" & Jahr & "#"
        ElseIf Monat < 5 Then
            AblaufVersicherung = "#04/30/" & Jahr & "#"
        Else
            AblaufVersicherung = "#00/00/0000#"
        End If
    Else
        AblaufVersicherung = "#00/00/0000#"
    End If
End Function

[tragger]

Ich weiß leider nicht ganz, was mich das nun weiter bringt. OK, das Ablaufdatum ist nun korrekt formatiert, damit es verglichen werden kann.

Aber er fragt mich nun für Datum_Heute nach einem Parameterwert und das Ablaufdatum und DAtum heute haben eine leicht unterschiedliche Formatierung. Kann ich bei 2 Daten in Stringform einen größer/kleiner-Vergleich machen?


Wie kann ich aber die andere Variante verwirklichen, die database ansprach mit dem Abfragefeld. Kann ich dort irgendwie die Funktion ausführen lassen? Diese soll dazu dienen, damit bei Eingabe keine Fehler im Ablaufdatum entstehen könnten und es sich somit immer errechnet. Ich weiß nur, dass man in Abfrage ein Feld erstellen kann, bei dem man z.B. Preis*Menge nimmt oder so...

Bin leider gerade etwas hilflos.....

[tragger]

Ich muss noch mal genauer nachfragen, da ich leider immernoch dort hänge...

Ein Verschieben von Datum_Heute oder des Funktionsaufrufes in die Spaltenliste der Abfrage bringt allerdings auch nichts.
Somit musst du aus der qryVersicherung ein Feld für den Vergleich beziehen, entweder eines, dass du mit dem aktuellen Datum (Datum_Heute) oder eines, dass du mit deinem Funktionsrückgabewert vergleichen kannst.

Ich muss für diese Abfrage, die Basis eines Formulares ist, doch das Feld auch in der Tabelle (Basis der Abfrage) haben. Oder kann man das anders machen? Kann man Werte in einer Abfrage speichern? Wenn ich das in der Tabelle speichere, würde ich sonst doch dem Datenbankprinzip widersprechen, dass man keine berechneten Werte in einer Tabelle speichern soll.

Vorschlag verlege das Ablaufdatum in die qryVersicherung und sortiere dann nach diesem Feld ... ORDER BY Ablaufdatum ... ---> wenn du in der qry die Funktion AS Ablaufdatum eingebaut hast

Da das Ablaufdatum ja berechnet werden muss, verstehe ich nicht, wie ich die Berechnung dafür anstoße.

[database]

Hallo,

Kann man Werte in einer Abfrage speichern

Nein, nicht speichern aber das Ergebnis einer Funktion kann man wie ein ganz normales Tabellenfeld anzeigen lassen.

Folgendes Problem taucht da im ganzen Salat auf ...

Nun möchte ich bei Klick auf einen Button meine Datensätze in Abhängigkeit des Ablaufdatums sortieren lassen

Du hast ja bereits eine Abfrage erstellt (qryVersicherung) die eine bestimmte Auswahl an Daten liefert.
Nun kannst du aber das Ergebnis dieser Abfrage weiter einschränken, in dem du eine weitere Abfrage  z.B. per VBA erstellst (so wie du es ja auch gemacht hast)
und hier eine Einschränkung mittels geeigneter WHERE - Klausel durchführst.
Die WHERE-Klausel, die du in deinem Beispiel erstellt hast ... kann nichts, - die erzeugt einen Fehler, da sie sich auf kein Feld der aktuellen Abfrage, also das Ergebnis der qryVersicherung, bezieht.

Wenn ich nun nochmal auf deinen Wunsch im Zitat eingehe, dann heißt das für mich, dass das Ergebnis der qryVersicherung entsprechend dem Ergebnis der Funktion AblaufVersicherung eingeschränkt werden soll.
Soweit sogut, somit muss das Ergebnis der Funktion bzw. deren Aufruf in die WHERE Klausel wandern - allerdings MUSS dort dann auch ein Vergleich mit einem Feld der qryVersicherung stattfinden, sonst bleibt die WHERE-Klausel zahnlos!

Zu dem soll dann noch eingeschränkt werden, dass nur Datensätze angezeigt werden sollen, deren Ablaufdatum in der Zukunft liegt?
Auf gut Deutsch sollte der SQL-Text deiner Abfrage in Etwa so lauten:

Zeige alle Felder der qryVersicherung und gib jene Datensätze aus, bei denen das qryVersicherungsfeld XYZ kleiner ist als das Ergebnis
der Funktion AblaufVersicherung und das aktuelle Datum kleiner ist als das qryVersicherungsfeld XYZ ist und sortiere nach VersicherungsNr aufsteigend

Der Vergleich 'kleiner ist als...' sei hier bitte exemplarisch zu sehen.

Der Knackpunkt ist nun jener, dass du ein Vergleichsfeld in der qryVersicherung haben MUSST. - ich nenne es exemplarisch 'SuperDatum' und würde davon ausgehend, dass der Code von DF6GL funktioniert (na no na net) folgenden Vorschlag machen:

Code [Auswählen] Erweitern


Public Function AblaufVersicherung(Start As Date, Laufzeit As Single) As String  
   Dim Monat As String
   Dim Tag As String
   Dim Jahr As String
   Dim Startgerechnet As Date
   Startgerechnet = Start - 1
   Jahr = DatePart("yyyy", Startgerechnet)
   If Laufzeit = 1 Then
       Jahr = Jahr + 1
       AblaufVersicherung =  "#04/30/" & Jahr & "#"
      ElseIf Laufzeit = 0,5 Then
       Monat = DatePart("m", Start)
       Tag = DatePart("d", Start)
       If Monat >= 5 And Monat <= 10 Then
           AblaufVersicherung = "#04/30/" & Jahr & "#"
       ElseIf Monat = 1 And Tag = 1 Then
           Jahr = Jahr + 1
           AblaufVersicherung = "#04/30/" & Jahr & "#"

       ElseIf Monat > 10 Then
           Jahr = Jahr + 1
           AblaufVersicherung = "#04/30/" & Jahr & "#"
       ElseIf Monat < 5 Then
           AblaufVersicherung = "#04/30/" & Jahr & "#"
       Else
           AblaufVersicherung = "#00/00/0000#"
       End If
   Else
       AblaufVersicherung = "#00/00/0000#"
   End If
End Function


...und hinter deinen Button dann ...

Code [Auswählen] Erweitern


Private Sub cmd_Aktuelle_Versicherung_Click()

   Dim sSQL as String
   Dim Datum_Heute As string
   Dim strAblauf as String

   'aktuelles Tagesdatum SQL-gerecht formatieren
   Datum_Heute = Format(Date(), "\#yyyy\-mm\-dd\#")
   'Laufzeit berechnen lassen
   strAblauf = AblaufVersicherung(Me!gültig_ab, Me!Versicherungsdauer)

   sSQL = " SELECT * FROM qryVersicherung WHERE qryVersicherung.SuperDatum <" &  strAblauf   & _
                " AND qryVersicherung.SupersDatum >" & Datum_Heute & " ORDER BY VersicherungsNr;"
   
   Me.RecordSource = sSQL

End Sub


Da ich ja nicht weiß welche Daten definitiv für dich dabei in Frage kommen könnten bitte die Vergleiche 'größer' und 'kleiner' in diesem Beispielcode rein exemplarisch anzusehen.
Wichtig ist, dass du in der WHERE-Klausel IMMER einen Vergleich anstellen MUSST- bezogen auf ein Feld aus dem SELECT-Teil !
Wäre doch gelacht, wenn wir das nicht hinbekommen *grrrr*  

HTH
Peter

[tragger]

Zu dem soll dann noch eingeschränkt werden, dass nur Datensätze angezeigt werden sollen, deren Ablaufdatum in der Zukunft liegt?
Auf gut Deutsch sollte der SQL-Text deiner Abfrage in Etwa so lauten:

Zeige alle Felder der qryVersicherung und gib jene Datensätze aus, bei denen das qryVersicherungsfeld XYZ kleiner ist als das Ergebnis
der Funktion AblaufVersicherung und das aktuelle Datum kleiner ist als das qryVersicherungsfeld XYZ ist und sortiere nach VersicherungsNr aufsteigend

Es ist nicht ganz so schlimm... Er braucht nur zu prüfen, ob Ablaufdatum größer heutiges Datum. Leider muss er dieses für jedes Datum(also jeden Datensatz) machen... Sehe ich das richtig, dass in deinem Vorschlag das Ablaufdatum einmalig berechnet wird??

Wenn ich den Code von dir falsch verstehe und er das für jeden Datensatz macht, muss ich in qryVersicherung nur ein Feld mit Date() ergänzen, oder??

Somit muss ich das Ablaufdatum an die Stelle des von dir genannten Superdatums bringen. Ich fürchte aber, dass sich das nicht einfach anzeigen lässt und es der geringste Aufwand ist, wenn ich die Tabelle und somit auch die qryVersicherung um dieses Feld ergänze und es bei Datensatzerzeugung/-änderung über mein Formular berechnen und füllen lasse??

Befürchte nämlich, dass sonst der Rechenaufwand zu hoch ist....

Edit: Habe die Abfrage um das heutige Datum ergänzt und den Rest übernommen. Dann wird der Ablauf nur für den aktuellen Datensatz und nicht für alle ermittelt.

[tragger]

OK, veränderter Ansatz:

Habe qryVersicherung überarbeitet und ein Feld Rechnerischer_Beginn ergänzt. Nun soll die Abfrage qryVersciherung außerdem ein FEld Ablauf ermitteln, damit ich das in der zweiten Abfrage später vergleichen kann:

Code [Auswählen] Erweitern

SELECT tabVersicherung.VersicherungsNr, tabVersicherung.TarifNr, tabVersicherung.VersicherungssummeEuro, tabVersicherung.gültig_ab, tabVersicherung.Versicherungsdauer, tabVersicherung.Bemerkung, tabVersicherung.Rechnerischer_Beginn, IIf([Versicherungsdauer]=1,DateAdd("yyyy", 1, [Rechnerischer_Beginn]),10) AS Ablauf
FROM tabVersicherung
ORDER BY tabVersicherung.VersicherungsNr;


Leider wird mir hier immer #Fehler ausgegeben, wenn die Versicherungsdauer 1 ist.

Wie muss ich hier denn das 1 Jahr oder 12 Monate hinzuaddieren?

Der zweite Schritt, dann erst, wenn der erste läuft

[tragger]

Fehler behoben. Durfte das Datum nicht bereits als String im Iso-Format speichern, da hier scheinbar die DateAdd Methode nicht geht.

Wen es interessiert:

Code [Auswählen] Erweitern

SELECT tabVersicherung.VersicherungsNr, tabVersicherung.TarifNr, tabVersicherung.VersicherungssummeEuro, tabVersicherung.gültig_ab, tabVersicherung.Versicherungsdauer, tabVersicherung.Bemerkung, tabVersicherung.Rechnerischer_Beginn, IIf([Versicherungsdauer]=0.5,(DateAdd("m",6,[Rechnerischer_Beginn]))-1,(DateAdd("yyyy",1,[Rechnerischer_Beginn]))-1) AS Ablauf
FROM tabVersicherung
ORDER BY tabVersicherung.VersicherungsNr;


So nun an die nächste Auswertung, die den Vergleich mit dem heutigen Datum machen soll.....

Ich nehme gerne auch Verbesserungsvorschläge zu dem bereits funktionierenden an....

[tragger]

Yes, yes...

es klappt....

Hier nun die SQL Anweisung für den Vergleich:

Code [Auswählen] Erweitern

Private Sub cmd_Aktuelle_Versicherung_Click()
    Dim sSQL As String
    Dim Datum_Heute As String

    'aktuelles Tagesdatum SQL-gerecht formatieren
    Datum_Heute = Format(Date, "\#yyyy\-mm\-dd\#")

    sSQL = " SELECT * FROM qryVersicherung" _
        & " WHERE qryVersicherung.Ablauf >" & Datum_Heute & "" _
        & " ORDER BY VersicherungsNr;"
   
    Me.RecordSource = sSQL
End Sub

Einen riesigen Dank! Besonders an database, auch für die Geduld mit mir, bin halt in der Tat auf dem Gebiet einfach Newbie

[database]

Hallo tragger,

wichtig ist, dass du mit einigem Aufwand letztendlich selbst die Lösung finalisiert hast.
Ganz wichtig bei der ganzen Sache, dass du aus dieser Sache Erkenntnisse für weitere Aufgabenstellungen mitnehmen kannst!


Grüße und viel Erfolg

Peter