Date Problem in VBA

[JMarcus]

I am having great difficulty trying to resolve this. WCCNF1 should turn to yes for previous dates in 2015 not 2016. I cant get it to work using -1. If Year = 2015 it should turn to Yes. Can anyone help me?
If (Year(Nz([WCCClmDt1])) = Year(Date)()) Then
WCCNF1 = "Yes"
ElseIf (Year(Nz([WCCMRDt1])) = Year(Date)()) And _
(Year(Nz([wccmrdtwgt])) = Year(Date)()) And _
(Year(Nz([wccmrdthgt])) = Year(Date)()) Then
WCCNF1 = "Yes"
Else
WCCNF1 = "No"
End If

 

[pbaldy]

Your parentheses are all goofed. Try

If Year(Nz([WCCClmDt1],0)) = Year(Date()) Then

 

[arnelgp]

If Year(Nz([WCCClmDt1])) = Year(Date())-1 Then
WCCNF1 = "Yes"
ElseIf Year(Nz([WCCMRDt1])) = Year(Date())-1 And _
Year(Nz([wccmrdtwgt])) = Year(Date())-1 And _
Year(Nz([wccmrdthgt])) = Year(Date())-1 Then
WCCNF1 = "Yes"
Else
WCCNF1 = "No"
End If

 

[JMarcus]

Interestingly I got it to work using the code below. The problem was the same flag was programmed on another exit at the bottom of the form which I didnt realize. Why is it accepting the format of Year(Date) - 1) when this is not supposed to?

If (Year(Nz([WCCClmDt1])) = Year(Date) - 1) Then
WCCNF1 = "Yes"
ElseIf (Year(Nz([WCCMRDt1])) = Year(Date) - 1) And _
(Year(Nz([wccmrdtwgt])) = Year(Date) - 1) And _
(Year(Nz([wccmrdthgt])) = Year(Date) - 1) Then
WCCNF1 = "Yes"
Else

 

[JMarcus]

Thanks for everyones response. Thankfully I was able to troubleshoot it but kind of interested in why it took Year(Date)-1) opposed to the other method.

 

[pbaldy]

Because this is invalid syntax:

Year(Date)())

 

[JMarcus]

If its invalid syntax then do you suggest I dont use it.

 

[JMarcus]

If it works how could it be invalid?

 

[Minty]

There is a difference between

Year(Date)()) _ Not correct
Year(Date()) _ Correct

However if you have a field called Date then it might work but you will get all sorts of problems because Date() is a inbuilt function to return today's date, and is a reserved word. Change the name of it to MyDate or StartDate or anything else that clearly identifies it as your field.

 

[pbaldy]

The invalid syntax I was referring to was having an extra set of parentheses outside the function:

Year(Date)()

 

[JMarcus]

I cant get Year (Date)()) to work properly but this works fine now.


If (Year(Nz([WCCClmDt1])) = Year(Date) - 1) Then
WCCNF1 = "Yes"
ElseIf (Year(Nz([WCCMRDt1])) = Year(Date) - 1) And _
(Year(Nz([wccmrdtwgt])) = Year(Date) - 1) And _
(Year(Nz([wccmrdthgt])) = Year(Date) - 1) Then
WCCNF1 = "Yes"
Else
WCCNF1 = "No"
End If

 

[Minty]

Sorry - in VBA it strips the () after the Date function in the editor. I had it in my head that this was in a query... My Bad.

 

[JMichaelM]

Got it. Inspiring that I was able to figure it out. You guys are awesome explaining the reasoning.!