Solved INSERT INTO add dates as 1900 years (1 Viewer)

[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?

 

[Pat Hartman]

Do you really need to convert it to a string?

When you create your SQL in VBA, unless you are referring to a field or a control, the date will be 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.

 

[Pat Hartman]

It can be very confusing.
This is a string and therefore MUST be enclosed in #'s.
"SELECT PkID, " & ToAccessDate(DateAdd("d", i, dt - 1)) & " As Expr1, 0 AS Expr3 FROM tblTemp " & _
so it should be:
"SELECT PkID, #" & ToAccessDate(DateAdd("d", i, dt - 1)) & "# As Expr1, 0 AS Expr3 FROM tblTemp " & _
However:
"SELECT PkID, ToAccessDate(DateAdd("d", i, Forms!yourform!dt - 1)) As Expr1, 0 AS Expr3 FROM tblTemp " & _
Is using actual objects with date data type formats ASSUMING that the function specifically has its return value defined as DateTime and so the dates are not strings, they are dates. BUT, in the OP's example, dt is a variable not a form control so this format wouldn't work. I just used it as an example. You can't reference a variable in an SQL String because SQL doesn't know VBA.

 

[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.