By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

date population based on more than 2 cell values

P: 1
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?
Expand|Select|Wrap|Line Numbers
  1. Private Sub ECR_RAISE_DATE_AfterUpdate()
  2.     If Not IsNull(Me.ECR_RAISE_DATE) Then
  3.         Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 15
  4.     End If
  5.     If Me.CATAGORY = "MEDIUM" Then
  6.         Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 60
  7.     End If
  8.     If Me.CATAGORY = "Major" Then
  9.         Me.ECR_TARGET_DATE = Me.ECR_RAISE_DATE + 120
  10.     End If
  11. End Sub
Jun 19 '17 #1

✓ answered by 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ECR_RAISE_DATE_AfterUpdate()
  2.     With Me
  3.         If IsNull(.ECR_RAISE_DATE) Then Exit Sub
  4.         .ECR_TARGET_DATE = DateAdd("d", Switch(.Category = "Major", 120 _
  5.                                              , .Category = "Medium", 60 _
  6.                                              , True, 15), .ECR_RAISE_DATE)
  7.     End With
  8. End Sub

Share this Question
Share on Google+
2 Replies


PhilOfWalton
Expert 100+
P: 1,430
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
Expand|Select|Wrap|Line Numbers
  1.     Exit Sub
  2.  
That way if the category is MEDIUM, you won't wast effort checking if it is MAJOR.

Phil
Jun 19 '17 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ECR_RAISE_DATE_AfterUpdate()
  2.     With Me
  3.         If IsNull(.ECR_RAISE_DATE) Then Exit Sub
  4.         .ECR_TARGET_DATE = DateAdd("d", Switch(.Category = "Major", 120 _
  5.                                              , .Category = "Medium", 60 _
  6.                                              , True, 15), .ECR_RAISE_DATE)
  7.     End With
  8. End Sub
Jun 19 '17 #3

Post your reply

Sign in to post your reply or Sign up for a free account.