Solved VBA Date issue

[PatAccess]

Hello Guys,
I always have some issues with my date functions. I am trying to get a field to turn red if the date is within 2019 but it is not working. what am I doing wrong? and what if I want it to turn blue if we are in the current year?
Here is my code

Private Sub Detail_Click()
Dim curYear As Date
Dim lastYear As Date

curYear = DatePart("yyyy", Me.Expires)

If DateTime.DateAdd("yyyy", -1, curYear) Then
    Me.Expires.ForeColor = vbRed
End If

End Sub

Thank you,

 

[theDBguy]

Hi. Have you tried just using the Year() function?

 

[PatAccess]

Hi. Have you tried just using the Year() function?

Earlier I did yes. I had

Private Sub Detail_Click()
Dim curYear As Date
Dim lastYear As Date

curYear = Year(Me.Expires)
lastYear = curYear - 1

If curYear Then
    Me.Expires.ForeColor = vbRed
End If

End Sub

that did not work either

 

[cheekybuddha]

If curYear Then 
  Me.Expires.ForeColor = vbRed 
End If

Try:

' ...
Dim curYear As Integer
' ...
If curYear = 2019 Then 
  Me.Expires.ForeColor = vbRed 
End If

hth,

d

 

[theDBguy]

Earlier I did yes. I had

Private Sub Detail_Click()
Dim curYear As Date
Dim lastYear As Date

curYear = Year(Me.Expires)
lastYear = curYear - 1

If curYear Then
    Me.Expires.ForeColor = vbRed
End If

End Sub

that did not work either

Hi. Let's do a quick check. What do you get with this?

MsgBox Year(Me.Expires)

 

[PatAccess]

Hi. Let's do a quick check. What do you get with this?

MsgBox Year(Me.Expires)

I get the correct year. I got it! It worked by using the DateDiff(). It worked with this code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim curYear As Integer
Dim lastYear As Integer

'curYear = DatePart("yyyy", Me.Expires)
curYear = DateDiff("yyyy", Date, Me.Expires)

If curYear < 0 Then
    Me.Expires.ForeColor = vbRed
    Me.Expires.FontBold = True
Else
    Me.Expires.ForeColor = vbBlack
End If
End Sub

Thank you so much guys

 

[theDBguy]

I get the correct year. I got it! It worked by using the DateDiff(). It worked with this code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim curYear As Integer
Dim lastYear As Integer

'curYear = DatePart("yyyy", Me.Expires)
curYear = DateDiff("yyyy", Date, Me.Expires)

If curYear < 0 Then
    Me.Expires.ForeColor = vbRed
    Me.Expires.FontBold = True
Else
    Me.Expires.ForeColor = vbBlack
End If
End Sub

Thank you so much guys

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[Isaac]

I get the correct year. I got it! It worked by using the DateDiff(). It worked with this code

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim curYear As Integer
Dim lastYear As Integer

'curYear = DatePart("yyyy", Me.Expires)
curYear = DateDiff("yyyy", Date, Me.Expires)

If curYear < 0 Then
    Me.Expires.ForeColor = vbRed
    Me.Expires.FontBold = True
Else
    Me.Expires.ForeColor = vbBlack
End If
End Sub

Thank you so much guys

I'm glad you got it working. FYI, your earlier method - except as adjusted by cheekybuddha in this post, is about a third as complicated and would work too..