date population based on more than 2 cell values

[karnav24]

Hi.

I need help in resolving my query.

I want to update my target date based on it's raise date.
but I want to set my target date based on my task severity [i.e.Minor(15 days period), Medium(60 days period), Major(120 days period)].

So for that I have written one syntax, but it was not working.
can anyone help me out?

Private Sub ECR_RAISE_DATE_AfterUpdate()
    If Not IsNull(Me.ECR_RAISE_DATE) Then
        Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 15
    End If
    If Me.CATAGORY = "MEDIUM" Then
        Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 60
    End If
    If Me.CATAGORY = "Major" Then
        Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 120
    End If
End Sub

[PhilOfWalton]

Two points.

1) Have a look at the DateAdd Function. If you think about it, ECR_RAISE_DATE is a date time field, so does ECR_RAISE_DATE + 15 mean add 15 seconds, 15 hours, 15 days, 15 years or what?

2) On the MEDIUM Category, before the End If add

    Exit Sub
 

That way if the category is MEDIUM, you won't wast effort checking if it is MAJOR.

Phil

[NeoPa]

Hi Karnav.

If you would like help with a problem then it makes sense to explain what the problem is first. Saying something doesn't work without including any details is something we unfortunately see a lot of. I'm not sure why it isn't obvious to everyone but that's like a police despatcher getting an emergency call and being told there's a problem, just before the caller hangs up. It's hard to help with a problem without any clues as to what the problem is.

From what I can see the code looks workable. Category is mis-spelled, but I suspect that's also true of the Control on the form so they should match anyway. The code could be written more clearly and concisely, as I've shown below, but that doesn't mean your current code shouldn't work.

Perhaps with more in the way of what went wrong and where we could be of greater help.

Private Sub ECR_RAISE_DATE_AfterUpdate()
    With Me
        If IsNull(.ECR_RAISE_DATE) Then Exit Sub
        .ECR_TARGET_DATE = DateAdd("d", Switch(.Category = "Major", 120 _
                                             , .Category = "Medium", 60 _
                                             , True, 15), .ECR_RAISE_DATE)
    End With
End Sub