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)

 

[Uncle Gizmo]

I assume that "Expires" is a textbox, however because you haven't used a naming convention it's unclear! This would be much better:- "txtExpires".. The point is, if Expires is a label, and you are trying to set the back colour, then you have to make sure that the label background is set to Normal and not "Transparent"... Transparent is the default, hence you will not see the change in back colour if you haven't changed it from Normal to Transparent...

More info on My Website HERE:-

Option Group Label Color Change - Nifty Access

Option Group Label Color Change The technique employed in this sample is well worth learning and applying in your own MS Access database. There's always a gotcha when you are developing new code, and I hit one when I was trying to build this.I wrote the code to change the label colours, but it...

www.niftyaccess.com

 

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