Append Year to table

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Append Year to table

Post by burrina »

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

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append Year to table

Post by HansV »

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

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Append Year to table

Post by burrina »

sdte is a number field
tblYears
2020
2021
2022

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Append Year to table

Post by HansV »

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

User avatar
burrina
4StarLounger
Posts: 550
Joined: 30 Jul 2014, 23:58

Re: Append Year to table

Post by burrina »

I was way off. The solution always is simpler than my approach, I hate that!

Thanks so much . . .