Need help updating a date field

[John Conklin]

Hello all,

I need help updating a date field on a form based on criteria entered on a
sub-form.

I have a form that has a target date for an issue, and a sub form we are
using to try and add an issue weight.

If the issue weight on the sub-form is equal to a certain number I need to
update the target date on the main form accordingly.

If the issue weight is:
5 I need to add 20 business days to the existing target date,
4 I need to add 15 business days to the existing target date,
3 I need to add 10 business days to the existing target date,
2 I need to add 5 business days to the existing target date, or
1 I need to add 24 hours or one business days to the existing target date

I tried it with an afterupdate event on the issue weight on my sub-form like
this:

If Me.IssueWeight >= 5 Then
Forms![frmIssueLog].Form![Target Date] =
Forms![frmIssueLog].Form![Target Date] + 20
ElseIf Me.IssueWeight = 4 Then
Forms![frmIssueLog].Form![Target Date] =
Forms![frmIssueLog].Form![Target Date] + 15
ElseIf Me.IssueWeight = 3 Then
Forms![frmIssueLog].Form![Target Date] =
Forms![frmIssueLog].Form![Target Date] + 10
ElseIf Me.IssueWeight = 2 Then
Forms![frmIssueLog].Form![Target Date] =
Forms![frmIssueLog].Form![Target Date] + 5
Else: MForms![frmIssueLog].Form![Target Date] =
Forms![frmIssueLog].Form![Target Date] + 1
End If

But this isn't update the date field.

Any help or suggestions oare greatly appreciated.

Thanks,
~John

 

[John Vinson]

Hello all,

I need help updating a date field on a form based on criteria entered on a
sub-form.

I have a form that has a target date for an issue, and a sub form we are
using to try and add an issue weight.

If the issue weight on the sub-form is equal to a certain number I need to
update the target date on the main form accordingly.

If the issue weight is:
5 I need to add 20 business days to the existing target date,
4 I need to add 15 business days to the existing target date,
3 I need to add 10 business days to the existing target date,
2 I need to add 5 business days to the existing target date, or
1 I need to add 24 hours or one business days to the existing target date

The Choose() function is ideal for this purpose: it takes as its first
argument an integer, and returns the value indexed by that integer:

DateAdd("d", Choose([Issue Weight], 1, 5, 10, 15, 20), [TargetDate])


John W. Vinson[MVP]

 

[John Conklin]

Thanks John,

Can you show me how I would use this?

Do I just add this to my if statement?

~John

Hello all,

I need help updating a date field on a form based on criteria entered on a
sub-form.

I have a form that has a target date for an issue, and a sub form we are
using to try and add an issue weight.

If the issue weight on the sub-form is equal to a certain number I need to
update the target date on the main form accordingly.

If the issue weight is:
5 I need to add 20 business days to the existing target date,
4 I need to add 15 business days to the existing target date,
3 I need to add 10 business days to the existing target date,
2 I need to add 5 business days to the existing target date, or
1 I need to add 24 hours or one business days to the existing target

date

The Choose() function is ideal for this purpose: it takes as its first
argument an integer, and returns the value indexed by that integer:

DateAdd("d", Choose([Issue Weight], 1, 5, 10, 15, 20), [TargetDate])


John W. Vinson[MVP]

 

[John Vinson]

Thanks John,

Can you show me how I would use this?

Do I just add this to my if statement?

You don't NEED the If statement. The Choose() function I cited returns
the new target date. Simply run an update query updating to that
function. No VBA at all.


John W. Vinson[MVP]