Auto calc last day of month based on date input into other field 2

[frosty2000]

Hello,

I have created an Access form to track the benefit selections of our employees. Our employee's benefits coverage ends on the last day of the month in which they terminate. (Therefore, if someone terminates on 2/1/05, benefits would end on 2/28/05. Or, if they term on 2/28/05, they would end on 2/28/05).

I have a field TerminationDate that tracks the employee's termination date. I also have a checkbox field EmployeeMedicalCoverage that is checked "Yes" if the member is enrolled in a medical plan. After the user inputs a date into the TerminationDate field, I want the system to automatically input the coverage end date into the EmployeeMedicalCoverageEndDate field, provided that field is already blank and the checkbox is True. (However, if that field already has a date in it or if the checkbox is False, then the system should do nothing).

Is this possible to do? I have done some research on the "Date Serial" function but don't really understand how to apply it to my needs. Below is the code I've started, but it's not doing anything. Thanks for your help.
frosty2000


Private Sub TerminationDate_AfterUpdate()
If [TerminationDate] <> Null And [EmployeeMedicalCoverage] = True And [EmployeeMedicalCoverageEndDate] = Null Then
[EmployeeMedicalCoverageEndDate] = DateSerial(Year([TerminationDate]), Month([TerminationDate] + 1), 0)
End If
End Sub

 

[GingerR]

last day of the month (sub your field for 'text0')

=DateAdd("m",1,CDate(Month([text0]) & "/01/" & Year([text0])))-1

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[frosty2000]

GingerR - You are awesome! Thank you!

I was able to get the below code to work if I left off the [EmployeeMedicalCoverageEndDate] = Null criteria. However, I need to keep this in there because if a coverage end date has been entered already, I don't want to overwrite it. With the below code, I'm not getting any error messages, but it isn't doing anything.

Again, I think the problem is with the [EmployeeMedicalCoverageEndDate]= Null criteria. Does anyone have any ideas?

Private Sub TerminationDate_AfterUpdate()
If [EmployeeMedicalCoverageEndDate] = Null And [TerminationDate] > 1 / 1 / 1900 And [EmployeeMedicalCoverage] = True Then
[EmployeeMedicalCoverageEndDate] = DateAdd("m", 1, CDate(Month([TerminationDate]) & "/01/" & Year([TerminationDate]))) - 1
End If
End Sub

 

[traingamer]

Try

 If IsNull([EmployeeMedicalCoverageEndDate])

rather than

 If [EmployeeMedicalCoverageEndDate] = Null

traingamer

 

[frosty2000]

Traingamer- Thank you! That solved my problem!

Also, for future reference, if I wanted to change the code to IsNotNull, what would the code look like?

frosty2000

 

[RoyVidar]

There's only IsNull (well, and a couple of other tests for null...), but negate it:

[tt]If Not IsNull([EmployeeMedicalCoverageEndDate])[/tt]

But, you'd probably need to do something with the date testing too

[tt]...And [TerminationDate] > #1/1/1900# And...[/tt]

Roy-Vidar

 

[PHV]

Just out of curiosity, why have you changed this ?
EmployeeMedicalCoverageEndDate] = DateSerial(Year([TerminationDate]), Month([TerminationDate] + 1), 0)