Coding Question

[fredk]

I am an extreme novice @ coding - I need help with the following...

I have a text box on a form called detailed effective date - I want to update two other fields based on this field - I put the following code in the after update event of the detailed effective date field

Dim dtDetEffDate As Date
dtDetEffDate = [Detailed Effective Date]
If dtDetEffDate >= &quot;1/1/2002&quot; And dtDetEffDate<= &quot;1/31/2002&quot; Then
[Effective Date] = &quot;January&quot;
ElseIf dtDetEffDate >= &quot;2/1/2002&quot; And dteDetEffDate <= &quot;2/31/2002&quot; Then
[Effective Date] = &quot;February&quot;
ElseIf dtDetEffDate >= &quot;3/1/2002&quot; And dteDetEffDate <= &quot;3/31/2002&quot; Then
[Effective Date] = &quot;March&quot;
Else: MsgBox (&quot;You entered an invalid date&quot
End If
End Sub

THis works for 1/1 through 2/29 but for march groups it is putting in a &quot;february&quot; result.

Any idea what I am doing wrong???

Thanks for all of your help.

 

[jebry]

Hi!

I think it is the <= &quot;2/31/2002&quot; that is throwing things off since February only has 29 days. Another possible way of doing this is:

Select Case Format([Detailed Effective Date], &quot;mmm&quot
Case &quot;JAN&quot;, &quot;FEB&quot;, &quot;MAR&quot;
[Effective Date] = Format([Detailed Effective Date], &quot;mmmm&quot
Case Else
MsgBox (&quot;You entered an invalid date&quot
End Select

hth Jeff Bridgham
bridgham@purdue.edu

 

[fredk]

Jeff - That is exactly what it was - (I was thinking 29 in the case of leap year)

I really appreciate your assistance!!!! Thanks for the Case Select also, I actually tried that first but could not figure it out - Thanks again!!!

fred

 

[cmmrfrds]

A simple way to get the last day of the month is to subtract 1 from the first day in the next month.

DateAdd(&quot;d&quot;, -1, nextMon) '-- subtract 1 day to get end of first month

Your data
And dteDetEffDate <= DateAdd(&quot;d&quot;, -1,&quot;3/1/2002&quot

 

[fredk]

Thanks for the information -I do have one other question for you - The procedure runs when the afterupdate event occurs on the detailedeffectivedate field on the form - The procedure inputs the &quot;month&quot; (&quot;january&quot;, &quot;february&quot;, etc) based on the date - my criteris is &quot;1/1&quot; or 1/31&quot; - Why does this not work when I enter a year other than 2002 -

In other words, if I enter 1/1/2002, the month updated without a problem....but if I enter 1/1/2003, it does not update the month field - ?? I am concerned for next year... I thought since the date structure is just &quot;1/1&quot; and not &quot;1/1/02&quot; that the month would update regardless of the year in the detailedeffectivedate field ???

Thanks for all of your help!!!

 

[jebry]

Hi!

I'm not sure what is happening but if you will post your code again as you have it right now, I will look at it!

hth
Jeff Bridgham
bridgham@purdue.edu