Tabelle per VBA füllen - Was mache ich falsch

[Andreas Irmer]

Hallo in die Runde,

ich möchte eine Tabelle per VBA Sub mit Werten befüllen. Dazu habe ich diverse Tipps aus dem Web berücksichtigt und ausprobiert. Leider komme ich so nicht weiter.

Hintergrund:
Ich möchte eine Tabelle tblKalender nutzen, in der ich alle Tage vom 01.01. bis 31.12. und dann auch der folgenden Jahre erfasse.

Damit ich die Tabelle nutzen kann, möchte ich die Werte eines vollen Jahres in die Tabelle schreiben. Bei Bedarf solen die Tage des nächsten Jahres nachgetragen werden.

Die Tabelle ist angelegt und die Felder sind angelegt

• ID als Autowert
• Datum als kurzer Text (war auch schon als Datum/Uhrzeit angelegt
• Wochentag als Zahl (Single)
• Woche als Zahl (Single)

Außerdem noch diverse andere Felder, die ich für Berechnungen brauche.

Am liebsten würde ich die Felder Datum, Wochentag und Woche als Kalenderwoche automatisch befüllen.

Ich habe aber zuerst versucht "nur" das Datum zu schreiben. Hier mein Code:

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit

Public Sub fncAlleTageFuellen(Jahr As Integer)
Dim db As DAO.Database
Dim I As Integer
Dim vDatum As Date
    Set db = CurrentDb
    vDatum = CDate("01.01." & Jahr)
     For I = 1 To 365
        db.Execute "INSERT INTO tblKalender(Datum) VALUES(vDatum);"
        vDatum = vDatum + 1
     Next I
     Set db = Nothing
    MsgBox "Fertig"
End Sub

Ich erhalte jedoch die Fehlermeldung 3061: 1 Parameter wurden erwartet aber es wurden zu wenig Parameter übergeben.

Wo fehlt das Komma....

Wenn ich einen Tipp bekommen könnte, wie ich das Feld Wochentag (1 bis 7) und das Feld Woche (1 bis 52/53) füllen kann ist auch das natürlich herzlich willkommen.

Was mache ich falsch hier?

[MzKlMu]

Hallo,
Datum ist eine Datum und kein Kurzer Text.
Wochentag und Woche sind Ganzzahlen (Integer) und und keine Kommazahl (Single). Oder hast Du schon mal den 5,5 ten Wochentag gesehen ?
Mit 365 Tagen fehlt Dir jedes Schaltjahr ein Tag.
Die serienmäßige Funktion für die Kalenderwoche kann nicht verwendet werden, die stimmt in manchen Jahren nicht.
Hier muss eine eigene Funkion verwendet werden.
Kurz zu Deinem Fehler: Werte einer Variablen müssen verkettet werden (mit &). Außerdem müssen Datumswerte nach US Norm (mm/tt/jjjj) oder nach ISO (jjjj/mm/tt) formatiert werden. Das habe ich umgangen indem ich den Datumswert nach Double gewandelt habe, in Datum ist nämlich in Wirklichkeit eine Kommazahl (Double).
Nach Anpassung der Dim Zeilen sieht die entsprechende Zeile so aus:

Code [Auswählen] Erweitern

db.Execute "INSERT INTO tblKalender(Datum) VALUES(" & CDbl(vDatum) & ")"

Hier mal Code zum Füllen einer Kalendertabelle (mit Schaltjahr) und mit der KW nach DIN bzw. ISO (wie ich das machen würde).

Code [Auswählen] Erweitern

Public Function fncAlleTageFuellen()
'Public Function fncAlleTageFuellen(intStartJahr As Integer, intEndeJahr As Integer) 'intStartJahr und
 'intEndeJahr aus Formularfeldern (alternativ zu Zeile 1)
Dim rs As dao.Recordset, lngTage As Long
Dim intStartJahr As Integer, intEndeJahr As Integer
intStartJahr = 2020: intEndeJahr = 2030
Set rs = CurrentDb.OpenRecordset("tblKalender")
'CurrentDb.Execute "DELETE * FROM tblKalender ", 128 'Bei Bedarf die Tabelle vorher löschen.
    Do
        rs.AddNew
        rs!TDatum = DateSerial(intStartJahr, 1, 1) + lngTage
        rs!WT = Weekday(rs!TDatum, vbMonday)
        rs!KW = IsoWeekAndYear(DateSerial(intStartJahr, 1, 1) + lngTage)
        If Year(rs!TDatum) > intEndeJahr Then Exit Do
        lngTage = lngTage + 1
        On Error Resume Next
        rs.Update
    Loop
    MsgBox "Fertig"
End Function
Hier die Funktion für die richtige KW

Code [Auswählen] Erweitern

Function IsoWeekAndYear(ByVal weekDate As Date, Optional ByVal separator As String = "-") As String
    Dim week As Integer
    Dim weekYear As Integer
    week = IsoWeek(weekDate)
    weekYear = Year(weekDate)
    If week >= 52 And Month(weekDate) = 1 Then
        weekYear = weekYear - 1
    ElseIf week = 1 And Month(weekDate) = 12 Then
        weekYear = weekYear + 1
    End If
    IsoWeekAndYear = weekYear & separator & Format(week, "00")
'    IsoWeekAndYear = Format(week, "00") & separator & weekYear
End Function

Code [Auswählen] Erweitern

Public Function IsoWeek(ByVal weekDate As Date) As Integer
' Workaround for Wrong Week Number for last Monday in Year - based on https://support.microsoft.com/en-us/kb/200299
    Dim retVal As Integer
    retVal = Format(weekDate, "ww", vbMonday, vbFirstFourDays)
    If retVal > 52 Then
        If Format(DateAdd("d", 7, weekDate), "ww", vbMonday, vbFirstFourDays) = 2 Then
            retVal = 1
        End If
    End If
  IsoWeek = retVal
End Function

[Beaker s.a.]

Hallo Klaus,

Code [Auswählen] Erweitern

IsoWeek(weekDate)Kannst du diese Function noch nachreichen?

gruss ekkehard

[MzKlMu]

Hallo,
habe es oben nachgereicht. Hatte ich übersehen. Danke für den Hinweis.

[Beaker s.a.]

Man hilft wo man kann.

[Andreas Irmer]

Hallo Klaus, Hallo Beaker,

vielen Dank für die Erklärung. Ich habe das jetzt versucht umzusetzen.
Die Felder in der Tabelle tblKalender sind jetzt:
ID - Autowert
Datum - Datum kurz TT.MM.JJJ
Wochentag - Integer
Feiertag - kurzer Text
KW - Integer

Dann habe ich den Code übernommen und die Kalenderfelder angepasst.

Code [Auswählen] Erweitern

Option Compare Database
Option Explicit

Public Function IsoWeek(ByVal weekDate As Date) As Integer
' Workaround for Wrong Week Number for last Monday in Year - based on https://support.microsoft.com/en-us/kb/200299
    Dim retVal As Integer
    retVal = Format(weekDate, "ww", vbMonday, vbFirstFourDays)
    If retVal > 52 Then
        If Format(DateAdd("d", 7, weekDate), "ww", vbMonday, vbFirstFourDays) = 2 Then
            retVal = 1
        End If
    End If
  IsoWeek = retVal
End Function

Function IsoWeekAndYear(ByVal weekDate As Date, Optional ByVal separator As String = "-") As String
    Dim week As Integer
    Dim weekYear As Integer
    week = IsoWeek(weekDate)
    weekYear = Year(weekDate)
    If week >= 52 And Month(weekDate) = 1 Then
        weekYear = weekYear - 1
    ElseIf week = 1 And Month(weekDate) = 12 Then
        weekYear = weekYear + 1
    End If
    IsoWeekAndYear = weekYear & separator & Format(week, "00")
'    IsoWeekAndYear = Format(week, "00") & separator & weekYear
End Function


Public Function fncAlleTageFuellen()
'Public Function fncAlleTageFuellen(intStartJahr As Integer, intEndeJahr As Integer) 'intStartJahr und
 'intEndeJahr aus Formularfeldern (alternativ zu Zeile 1)
Dim rs As dao.Recordset, lngTage As Long
Dim intStartJahr As Integer, intEndeJahr As Integer
intStartJahr = 2022: intEndeJahr = 2032
Set rs = CurrentDb.OpenRecordset("tblKalender")
'CurrentDb.Execute "DELETE * FROM tblKalender ", 128 'Bei Bedarf die Tabelle vorher löschen.
    Do
        rs.AddNew
        rs!Datum = DateSerial(intStartJahr, 1, 1) + lngTage
        rs!Wochentag = Weekday(rs!Datum, vbMonday)
        rs!KW = IsoWeekAndYear(DateSerial(intStartJahr, 1, 1) + lngTage)
        If Year(rs!Datum) > intEndeJahr Then Exit Do
        lngTage = lngTage + 1
        On Error Resume Next
        rs.Update
    Loop
    MsgBox "Fertig"
End Function


Wenn ich jetzt die Funktion aufrufe erhalte ich leider erneut einen Laufzeitfehler 3421 in der Zeile:

Code [Auswählen] Erweitern

rs!KW = IsoWeekAndYear(DateSerial(intStartJahr, 1, 1) + lngTage)
Liegt das daran, dass die Funktion IsoWeekAndYear die Variable weekDate als Datum erwartet?

Oder wo liegt der Fehler jetzt?

[MzKlMu]

Hallo,

Datum - Datum kurz TT.MM.JJJ

Welchen Datentyp hast Du für das Datunm verwendet ?
In der Tabelle ist eine Formateinstellung vollkommen unwichtig und somit überflüssig.
Bitte Format löschen und "Datum/Uhrzeit" als Datentyp einstellen.
Die Tabelle wird auch eine reine Nachschlagetabelle, ein Autowert wird da nicht gebraucht.
Alle Felder sollte man indizieren, das Datum eindeutig, also ohne Duplikate.

[Andreas Irmer]

Hallo Klaus,

das Datum war schon als Datum formatiert. Ich hatte nur das Format in der Tabelle auf Datum, kurz noch eingeschränkt.

Jetzt ist die Einschränkung raus aber die Fehlermeldung ist leider geblieben.

Danke Dir.

[MzKlMu]

Hallo,

das Datum war schon als Datum formatiert.

Mit dem Format hat das nichts zu tun.
Ich fragte auch nach dem Datentyp, daher noch mal die Frage: Wie ist der Felddatentyp des Datumsfeldes?
Und zitiere bitte immer den Text der Fehlermeldung, mit der Nr 3421 kann ich nichts anfangen.

[Andreas Irmer]

Hallo Klaus,

der Felddatentyp ist Datum/Uhrzeit und die Fehlermeldung lautet Datentyp-Konvertierungsfehler.

[Andreas Irmer]

Hallo zusammen, ich nochmal.

Code [Auswählen] Erweitern

Function IsoWeekAndYear(ByVal weekDate As Date, Optional ByVal separator As String = "-") As String

Wenn ich das richtig verstehe, dann gibt IsoWeekAndYear einen String zurück, soll aber in ein Integer Feld geschrieben werden. Ist es das vielleicht?

[Andreas Irmer]

Ok, zur Erklärung, ich habe jetzt die Function fncAlleTageFuellen geändert und statt der Zeile

Code [Auswählen] Erweitern

rs!KW = IsoWeekAndYear(DateSerial(intStartJahr, 1, 1) + lngTage)nun die Zeile

Code [Auswählen] Erweitern

rs!KW = IsoWeek(DateSerial(intStartJahr, 1, 1) + lngTage)verwendet. Und auf einmal, es klappt!

[MzKlMu]

Hallo,
kurze Erläuterung:
Die Funktion IsoWeekAndYear legt (wie der Name schon sagt) die Woche und das JAhr, durch einen Bindestrich getrennt. Und das muss in ein Textfeld, Integer geht da nicht, wie Du bemerkt hast. Es kann vorteilhaft sein, dass man erkennt dass z.B. die KW 53 teilweise im Folgejahr liegt oder die KW 1 noch im alten Jahr beginnt.
Die Funktion IsoWeek liefert nur die reine Zahl, da geht Integer, wenn Dir das reicht, ist es gut, wenn nicht musst Du in der Tabelle für die KW ein Textfeld anlegen und dann IsoWeekAndYear verwenden.

Ich hätte das schon früher erklären sollen, hatte ich nicht dran gedacht, sorry.