Solved INSERT INTO add dates as 1900 years

[DBUserNew]

Hello,

I have the below code, which works. But it insert dates as from 1900.

Dim dbs            As DAO.Database
Dim strSQL      As String
Dim i           As Long
Dim TotalDays   As Integer
Dim dt          As Date

Set dbs = CurrentDb
dt = Me.txtDateStart
TotalDays = Me.txtDateEnd - dt + 1

For i = 1 To TotalDays

strSQL = "INSERT INTO tblSchedule (PkID, PkDate, StatusID) " & _
         "SELECT PkID, " & ToAccessDate(DateAdd("d", i, dt - 1)) & " As Expr1, 0 AS Expr3 FROM tblTemp " & _
         "WHERE IsSelected=-1;"
dbs.Execute strSQL, dbFailOnError

Next i

MsgBox "done", vbInformation

dbs.Close
Set dbs = Nothing
DoCmd.Close acForm, Me.Name, acSaveNo

This is what I am trying to do.

1). In header, there are two text fields to enter start & end date (txtDateStart / txtDateEnd). Both are formatted in "mmm d, yyyy"
2). Subform detail has option to select required records with Yes/No controls.
3). Get number of days between start & end dates.
4). INSERT INTO tblPK with selected records & dates as txtDateStart + {1+2+n = totaldays)

eg: start date: Oct 1, 2023 | End date: Oct 10, 2023

When I run with the above code it insert the dates. But they are from 1900, 7/4/1905, 7/3/1905, 7/2/1905, 7/1/1905....6/25/1905
I don't know what am I doing wrong here?

ToAccessDate: It is a function from Northwind 2.0

Any assistance would be greatly appreciated.
Raj

 

[theDBguy]

What does ToAccessDate() look like?

 

[DBUserNew]

Public Function ToAccessDate(ByVal dt As Date) As String
    ToAccessDate = Format(dt, "yyyy-mm-dd")
End Function

 

[Josef P.]

For data problems, always ask this question first: What does the SQL statement created in VBA look like?

strSQL = "INSERT INTO tblSchedule (PkID, PkDate, StatusID) " & _
         "SELECT PkID, " & ToAccessDate(DateAdd("d", i, dt - 1)) & " As Expr1, 0 AS Expr3 FROM tblTemp " & _
         "WHERE IsSelected=-1;"
debug.print strSQL
stop
dbs.Execute strSQL, dbFailOnError

e. g.

SELECT PkID, 2023-10-01 As Expr1, 0 AS Expr3 FROM tblTemp ...

Can you see the mistake?

 

[DBUserNew]

Thank you Josef for your reply. Sorry, I am a newbie in Access VBA things. Should I add "#" between dates, I am not sure.

How can I INSERT those dates?

 

[DBUserNew]

Thank you Josef. Awesome. I added ' between dates and it works now. Thanks a lot Josef.

 

[Gasman]

Can't you just insert the date as a date?
Do you really need to convert it to a string?

 

[Gasman]

When you create your SQL in VBA, unless you are referring to a field or a control, the date will be a string.

Thanks Pat.

 

[ebs17]

T999 is a table with a field I (Long, PK) containing the sequential numbers from 0 to 999 (my standard number help table).
With such a table you can save a loop and thus VBA and thus the assembly of the SQL statement and thus additional effort due to the formatting that is then necessary.

PARAMETERS
   parStartDate Date,
   parEndDate Date
;
INSERT INTO
   tblSchedule(
      PkID,
      PkDate,
      StatusID
   )
SELECT
   T.PkID,
   DateAdd("d", X.I, parStartDate) AS Expr1,
   0 AS Expr3
FROM
   tblTemp AS T,
   T999 AS X
WHERE
   T.IsSelected = -1
      AND
   X.I <= parEndDate - parStartDate

In addition, you have one query for everything (one table access) instead of a whole number of individual queries.
The use of VBA can now be limited to executing the query and passing the parameters.