Inserting Dates (1 Viewer)

[mike60smart]

Hi Everyone

I have a Form which has an Unbound Control named "CurrYr" to display the Current Year 2025 using the following.

=Right(Date(),4)

I then want to run an Append Query using the Code as follows:

INSERT INTO tblCurrentPastYears ( YearClassID, StudentID, GradeID, ClassID )
SELECT tblYearClasses.ID, tblStudentDetails.StudentID, tblCurrentPastYears.GradeID, tblCurrentPastYears.ClassID
FROM tblYearClasses LEFT JOIN (tblStudentDetails LEFT JOIN (tblGradeList RIGHT JOIN tblCurrentPastYears ON tblGradeList.GradeListID = tblCurrentPastYears.GradeID) ON tblStudentDetails.StudentID = tblCurrentPastYears.StudentID) ON tblYearClasses.ID = tblStudentDetails.YearClassID
WHERE (((tblGradeList.GradeList)="Prep") AND ((tblStudentDetails.Active)=True))
ORDER BY tblGradeList.GradeList;

This works just fine and appends the required records.

I then need to update those records to insert the date "01 Jan 2026" into a field named "DateFrom"

How would I construct an Unbound Control using the Control "CurrYr" to display the value of "01 Jan 2026"

Your help appreciated

 

[CJ_London]

Perhaps use

“01 Jan “ & me.currYr

Not sure why you are using strings for dates or why you don’t just insert it rather than update after insert

 

[mike60smart]

Perhaps use

“01 Jan “ & me.currYr

Not sure why you are using strings for dates or why you don’t just insert it rather than update after insert

Hi CJ Many thanks nad now you have pointed me in the right direction I will do the insert.
Many thanks

 

[June7]

To extract year: Year(Date())

You want to add 1 to year and save 01 Jan 2026?

Yes, why not include this calc in the INSERT action and why not a date instead of string?
SELECT ..., '1/1/' & Year(Date()) FROM ...

 

[mike60smart]

To extract year: Year(Date())

You want to add 1 to year and save 01 Jan 2026?

Yes, why not include this calc in the INSERT action and why not a date instead of string?
SELECT ..., '1/1/' & Year(Date()) FROM ...

Hi June & CJ

I am trying to get the date updated by using the following Code and cannot get the syntax right,

Dim strSQL As String

strSQL = "UPDATE tblCurentPastYears SET DateFrom = #" & 1 / 1 / [txtY] _
       & " # WHERE DateFrom is Null;"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

The Immediate window displays the following

UPDATE tblCurentPastYears SET DateFrom = #4.93583415597236E-04 # WHERE DateFrom is Null;

 

[CarlettoFed]

strSQL = "UPDATE tblCurentPastYears SET DateFrom = #1/1/" & Me.txtY & " # WHERE DateFrom is Null;"

if in the txtY control it says 2026 otherwise use

strSQL = "UPDATE tblCurentPastYears SET DateFrom = #1/1/" & Year(Date()) & "# WHERE DateFrom is Null;"

 

[Josef P.]

1 / 2026 = 4.93583415597236E-04

Dim NewDateFrom as Date
NewDateFrom = DateSerial([txtY], 1, 1)

Dim strSQL As String
strSQL = "UPDATE tblCurentPastYears SET DateFrom = " & Format(NewDateFrom, "\#mm\/dd\/yyyy\#") & _
         " WHERE DateFrom is Null;"

 

[mike60smart]

Hi Everyone
As usual it was my mistake again

The initial


I changed the SQL Code shown below and it now works just fine

Dim strSQL As String

strSQL = "UPDATE tblCurrentPastYears SET DateFrom = # " & Me.txtD _
       & " # WHERE DateFrom is Null;"
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

I obtained the Me.txtD as follows

="1 / 1 / " & [txtCurrYr]

Many thanks for the help