I am trying to add the next year to the table if it does not already exists. Ex; When form loads and this year is not in tblYears, 2022 then add it
In other words the code would always add one more year to the table greater than the current year if it does not exists already.
Here is my feeble attempt:
Dim strSQL As String
strSQL = "UPDATE tblYears Set sdte = DateAdd("yyyy", -1, Year + 1 < Year()
CurrentDb.Execute strSQL, dbFailOnError
Append Year to table
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Append Year to table
Is sdte a number field (contents fore example 2021) or a date field (contents for example 1/1/2021 - perhaps formatted to display the year only)?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Append Year to table
sdte is a number field
tblYears
2020
2021
2022
tblYears
2020
2021
2022
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Append Year to table
For example:
Code: Select all
Private Sub Form_Load()
Dim lngYear As Long
Dim strSQL As String
lngYear = Year(Date)
If IsNull(Dlookup("sdte", "tblYears", "sdte=" & lngYear)) Then
strSQL = "INSERT INTO tblYears (sdte) VALUES(" & lngYear & ")"
CurrentDb.Execute strSQL, dbFailOnError
End If
End Sub
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 550
- Joined: 30 Jul 2014, 23:58
Re: Append Year to table
I was way off. The solution always is simpler than my approach, I hate that!
Thanks so much . . .
Thanks so much . . .