MS Access Problem with DateAdd in VBA Insert statement with inconsistent date format

I'm having problems as to how a date is being stored in my DB.

I need to create a temporary TableC into which will be inserted X records for each record on TableA. Where X is the number of weeks. The resulting table will be used in a UNION statement with several other queries.

The problem lies in how DateAdd creates the date for each new record. Dates are originally stored in dd/mm/yyyy format. But the resulting DateAdd("ww", i, rst![Date]) will sometimes be stored in mm/dd/yyyy format creating havoc in the 30K resulting rows.

Wrote a VB Sub to create the table and bellow is a sample as to how it is actually stored vs what was expected.

 
Sub AddItems()
    Dim db as DAO.Database
    Dim rst as DAO.Recordset
    Dim Sql1, Sql2 as String

    Sql1 = "SELECT [a].*, [b].[Date], [b].[Weeks]. [b].[Rate] FROM TableA as [a] LEFT JOIN TableB as [b] WHERE [a].[GroupId] = [b].[Id] ORDER BY [b].[Date], [a].[Id];"

    Set db = CurrentDb
    Set rst = db.OpenRecordset(Sql1)

    rst.movefirst
    While NOT rst.EOF
        If rst![Weeks] > 0 Then
            For i = 1 to rst![Weeks]
                Sql2 = "INSERT INTO TableC ([ID], [CUSTOMER], [DATE], [AMOUNT]) VALUES ("
                Sql2 = Sql2 & rst![ID] & ", " & rst![CUSTOMER]
                Sql2 = Sql2 & "#" & Format(DateAdd("ww", (i - 1), Format(rst![Date], "mm/dd/yyyy")), "mm/dd/yyyy") & "#"
                Sql2 = Sql2 & ", " & rst![AMOUNT]
                Sql2 = Sql2 & ")"
                Debug.Print Format(DateAdd("ww", (i - 1), Format(rst![Date], "mm/dd/yyyy")), "mm/dd/yyyy")
                db.Execute(Sql2)
            Next i
        End If
        rst.movenext
    Wend
End Sub  


RESULTING TABLE SAMPLE
+-------------+------------+------------+
| Week | On Table | On Debug | Expected Data Inserted
+-------------+------------+------------+
| 1 | 12/02/2019 | 02/12/2019 | Should be 2 / Dec / 2019
| | as 12/Feb | Correct |
+-------------+------------+------------+
| 2 | 12/09/2019 | 09/12/2019 | Should be 9 / Dec / 2019
| | as 12/Sept | Correct |
+-------------+------------+------------+
| 3 | 16/12/2019 | 16/12/2019 | Should be 16 / Dec / 2019
| | Correct | Correct |
+-------------+------------+------------+

The results printed on the Immediate Window are correct, yet on the actual table the information is incorrect. My sample data starts on 02/12/2019 (2 / December / 2019 as confirmed with the date selector on table view in TableA)

The debug window shows the correct information to be stored, yet on the table is incorrect eventhough the field [DATE] in TableC is formated with "Short Date" and with an IsDate validation rule.

Without the two Format statements the results where extremely skweed from what was expected. Yet the resulting DATE field isn't consistent.

IS THERE A WAY TO CONSISTENTLY GENERATE DATES AND STORE THEM ACCORDING TO THE SYSTEM'S SETTINGS ?

Solution

First some remarks:

Variable i wasn't declared at all, you should use Option Explicit in the header of the module to enable the VBE to warn you about.
Date is a reserved word, so I renamed it to YourDate.
Variable sql1 was implicitly defined as type variant.
Your definition of the SQL for variable sql1 wasn't syntactically correct. I corrected it and hope it is what you meant.
I expect that your table field YourDate is of type date and not string. If it is string then you need to convert it to date first in the code, or much better in the table.
Always use the property Value explicitly if you implicitly want to use it to clear what you do, and don't assign an object by accident.
Concatenating a SQL string is insecure in case of SQL Injection. The object oriented approach isn't and it is type safe.

This should work as you expect:

Option Compare Database
Option Explicit

Sub AddItems()
    Dim sql1 As String
    sql1 = "SELECT a.*, b.YourDate, b.Weeks, b.Rate FROM TableA as a LEFT JOIN TableB as b On a.GroupId = b.Id ORDER BY b.YourDate, a.Id"

    Dim db As DAO.Database
    Set db = CurrentDb

    Dim rst As DAO.Recordset
    Set rst = db.OpenRecordset(sql1)

    rst.MoveFirst
    While Not rst.EOF
        If rst("Weeks").Value > 0 Then
            Dim i As Long
            For i = 1 To rst("Weeks").Value
                With CurrentDb().CreateQueryDef(vbNullString, _
                    "INSERT INTO TableC ([ID], [CUSTOMER], [YourDATE], [AMOUNT]) " & _
                    "VALUES (@ParID, @ParCustomer, @ParDate, @ParAmount)")
                    .Parameters("@ParID").Value = rst("ID").Value
                    .Parameters("@ParCustomer").Value = rst("CUSTOMER").Value
                    .Parameters("@ParDate").Value = DateAdd("ww", (i - 1), rst("YourDate").Value)
                    .Parameters("@ParAmount").Value = rst("AMOUNT").Value
                    .Execute dbFailOnError
                End With
            Next i
        End If
        rst.MoveNext
    Wend

    rst.Close
End Sub