Solved VBA If statement to calculate the due date (1 Viewer)

[Teri Bridges]

Hi, I am trying to turn an expression into VBA code, I placed the following into the duedate expression builder .... =AddWorkingDays(ReviewDate) and it works. addworkingdays is a public function in myDb.

i would like to know how to write the VBA for this

Basically, I would like to say if the review date isNull don't do anything, if the review date is filled in AddWorkingdays (function) to the review date.

 

[theDBguy]

It should be the same expression as you have now, depending on how the function was written, if it can handle nulls or not.

 

[Teri Bridges]

It should be the same expression as you have now, depending on how the function was written, if it can handle nulls or not.

Private Sub txt_DueDate_AfterUpdate()
=AddWorkingDays([ReviewDate])
End Sub

that does not work. I thought I would need and if statement

 

[The_Doc_Man]

Your problem is simple. When you put an expression =XXXXX in a .ControlSource, the value goes to the control source. When you have that expression in VBA, you need a place to put it. Possibly, Me.txt_DueDate = AddWorkingDays([ReviewDate]) would do what you want, if you wanted to put the computed date in the text box. This would imply that EITHER the text box is originally unbound OR you are planning to override whatever value is already there via binding.

IF you were worried about nulls,

IF NZ( [ReviewDate], 0 ) = 0 THEN
    Me.txt_DueDate = {whatever you would put to show the Review Date was null}
ELSE
    Me.txt_DueDate = AddWorkingDays( [ReviewDate] )
END IF

 

[Teri Bridges]

Your problem is simple. When you put an expression =XXXXX in a .ControlSource, the value goes to the control source. When you have that expression in VBA, you need a place to put it. Possibly, Me.txt_DueDate = AddWorkingDays([ReviewDate]) would do what you want, if you wanted to put the computed date in the text box. This would imply that EITHER the text box is originally unbound OR you are planning to override whatever value is already there via binding.

IF you were worried about nulls,

IF NZ( [ReviewDate], 0 ) = 0 THEN
    Me.txt_DueDate = {whatever you would put to show the Review Date was null}
ELSE
    Me.txt_DueDate = AddWorkingDays( [ReviewDate] )
END IF

I want the due date left blank if there is no review date

Private Sub txt_DueDate_AfterUpdate()
If Nz([ReviewDate], 0) = 0 Then
Me.txt_DueDate = 0 { I do not know what to say here}
Else
Me.txt_DueDate = AddWorkingDays([ReviewDate])
End If
End Sub

 

[Teri Bridges]

Your problem is simple. When you put an expression =XXXXX in a .ControlSource, the value goes to the control source. When you have that expression in VBA, you need a place to put it. Possibly, Me.txt_DueDate = AddWorkingDays([ReviewDate]) would do what you want, if you wanted to put the computed date in the text box. This would imply that EITHER the text box is originally unbound OR you are planning to override whatever value is already there via binding.

IF you were worried about nulls,

IF NZ( [ReviewDate], 0 ) = 0 THEN
    Me.txt_DueDate = {whatever you would put to show the Review Date was null}
ELSE
    Me.txt_DueDate = AddWorkingDays( [ReviewDate] )
END IF

Your problem is simple. When you put an expression =XXXXX in a .ControlSource, the value goes to the control source. When you have that expression in VBA, you need a place to put it. Possibly, Me.txt_DueDate = AddWorkingDays([ReviewDate]) would do what you want, if you wanted to put the computed date in the text box. This would imply that EITHER the text box is originally unbound OR you are planning to override whatever value is already there via binding.

IF you were worried about nulls,

IF NZ( [ReviewDate], 0 ) = 0 THEN
    Me.txt_DueDate = {whatever you would put to show the Review Date was null}
ELSE
    Me.txt_DueDate = AddWorkingDays( [ReviewDate] )
END IF

I did try this and nothing happens

Private Sub txt_DueDate_AfterUpdate()
Me.txt_DueDate = AddWorkingDays([ReviewDate])
End Sub

 

[theDBguy]

Private Sub txt_DueDate_AfterUpdate()
=AddWorkingDays([ReviewDate])
End Sub

that does not work. I thought I would need and if statement

As I said earlier, it would also depend on how the AddWorkingDays() function was written - if it could handle nulls or not. Can you show us that function?

 

[Teri Bridges]

As I said earlier, it would also depend on how the AddWorkingDays() function was written - if it could handle nulls or not. Can you show us that function?

'Used to calculate the review cycle Due Date
Public Function AddWorkingDays(ByVal dtmDate As Variant, Optional NumberWorkDays = 3) As Date 'the #3 is the number of days for the review cycle

Dim i As Integer
If IsDate(dtmDate) Then
Do
dtmDate = Int(dtmDate) + 1
If WeekDay(dtmDate) <> vbSaturday And WeekDay(dtmDate) <> vbSunday Then
i = i + 1
End If
Loop Until i = NumberWorkDays
AddWorkingDays = dtmDate
End If
End Function

 

[Josef P.]

Private Sub txt_DueDate_AfterUpdate()
   Me.txt_DueDate = AddWorkingDays([ReviewDate])
End Sub

Is this the right place for the call?
You may want to fill txtDueDate after the ReviewDate has been changed.

Private Sub txt_ReviewDate_AfterUpdate()
   Me.txt_DueDate = AddWorkingDays(Me.txt_ReviewDate)
End Sub

 

[Teri Bridges]

Is this the right place for the call?
You may want to fill txtDueDate after the ReviewDate has been changed.

Private Sub txt_ReviewDate_AfterUpdate()
   Me.txt_DueDate = AddWorkingDays(Me.txt_ReviewDate)
End Sub

Okay do I was indeed making the call in the wrong field.

Thank you so much.

I feel like I ask a lot of stupid questions but I will say I am so thankful you guys are here. I am learning so much.