Auto Calculating date in relation to value in drop down

[Jun91]

Hi everyone,
I’d like to know how to auto calculate on a table depending on the drop-down value I chose, to show a specific date.
For example….

Drop down value: Small project, then start date + 20 days
auto calculated
Drop down value: large project, then start date + 60 day
auto calculated
Etc…

I have some minor experience with VBA, but am not fully educated in this mater so, would very much appreciate if you could explain step by step.

Thank you for your support in advance

 

[pbaldy]

You can adapt the second bit of code here:

http://www.baldyweb.com/ConditionalVisibility.htm

Using the DateAdd() function the appropriate number of days. That number could come from the combo if your setup allows it.

 

[Jun91]

Thank you for your response!! I have some follow-up questions.

1) Do i insert the "source name" into the "ControlName" ?
2) Where do I insert the "DateAdd()"?

Sorry these must be simple questions for someone more advanced...
tried my best below...

If [TextBox Projectsize] = "Small" Then
[TextBox Enddate] = DateAdd(20)
If [TextBox Projectsize] = "lagre" Then
[TextBox Enddate] = DateAdd(60)
Else
[TextBox Enddate] = Null
End If

 

[pbaldy]

In place of ControlName you'd use the name of your controls (textboxes, combos, whatever). You have DateAdd() in the right place, but you must not have researched its syntax.

https://support.office.com/en-us/article/DateAdd-Function-63befdf6-1ffa-4357-9424-61e8c57afc19

Oh, and your If/Then won't compile. Use ElseIf for the second test.

 

[Jun91]

Hi pbaldy,
thanks for getting back again. Let me try this again.

Dim StartDate As Date
StartDate = TextBox Startdate

If Me.[TextBox Projectsize] = "Small" Then
Me.[TextBox Enddate].Visible = DateAdd(d,20,StartDate)

ElseIf Me.[TextBox Projectsize] = "lagre" Then
Me.[TextBox Enddate].Visible = DateAdd(d,60,StartDate)

Else
Me.[TextBox Enddate] = Null
End If

Will this work...?

 

[pbaldy]

You didn't try? No, try:

Dim StartDate As Date
StartDate = Me.[TextBox Startdate]

If Me.[TextBox Projectsize] = "Small" Then
Me.[TextBox Enddate]= DateAdd("d",20,StartDate)

ElseIf Me.[TextBox Projectsize] = "lagre" Then
Me.[TextBox Enddate]= DateAdd("d",60,StartDate)

Else
Me.[TextBox Enddate] = Null
End If

 

[Jun91]

Hi pbaldy,
Thanks again for getting back. I've tried your revised code but it did not work… Just checking but is the correct place to save it under, form > text box property (for TextBox Enddate) > Event > On change > code builder ?

Hope you can help me get this going… Appreciate your patience and support.

The code i typed was...

Private Sub Enddate()
Dim StartDate As Date
StartDate = Me.[TextBox Startdate]

If Me.[TextBox Projectsize] = "Small" Then
Me.[TextBox Enddate]= DateAdd("d",20,StartDate)

ElseIf Me.[TextBox Projectsize] = "lagre" Then
Me.[TextBox Enddate]= DateAdd("d",60,StartDate)

Else
Me.[TextBox Enddate] = Null
End If
End Sub

 

[pbaldy]

The code would go in the after update event of the project size combo, as mentioned in the link. You also need to use the actual names of your controls. The code can be entered here:

http://www.baldyweb.com/FirstVBA.htm

 

[Jun91]

thank you !! finally got it going !!!

 

[pbaldy]

Happy to help!

 

[Jun91]

Sorry !! One last question !! what can i do to prevent the error which occurs when [TextBox Startdate] is blank?


Dim StartDate As Date
StartDate = Me.[TextBox Startdate]

If StartDate = Null then End

ElseIf Me.[TextBox Projectsize] = "Small" Then
Me.[TextBox Enddate]= DateAdd("d",20,StartDate)

ElseIf Me.[TextBox Projectsize] = "lagre" Then
Me.[TextBox Enddate]= DateAdd("d",60,StartDate)

Else
Me.[TextBox Enddate] = Null
End If

I don’t know the code to end the procedure....

 

[pbaldy]

The Date data type can't take a Null, so either prevent it:

StartDate = Nz(Me.[TextBox Startdate], 0)

And then test for 0 or allow it by declaring the variable as Variant, the only data type that can take Null:

Dim StartDate As Variant

 

[pbaldy]

Oh, and presuming you declare as Variant the test and exit would be:

If IsNull(StartDate) then Exit Sub

 

[Jun91]

Wow that got a bit complicated...
So, it would end up like,

Dim StartDate As Variant

StartDate = Nz(Me.[TextBox Startdate], 0)

If IsNull(StartDate) then Exit Sub

ElseIf Me.[TextBox Projectsize] = "Small" Then
Me.[TextBox Enddate]= DateAdd("d",20,StartDate)

ElseIf Me.[TextBox Projectsize] = "lagre" Then
Me.[TextBox Enddate]= DateAdd("d",60,StartDate)

Else
Me.[TextBox Enddate] = Null
End If

 

[pbaldy]

Well, it was either/or. Since you declared the variable as Variant, set it without the Nz():

StartDate = Me.[TextBox Startdate]

Otherwise your test for Null will never be met.

 

[Jun91]

Thanks for all your support. One final question.
If I use,

If IsNull(StartDate) Then Exit Sub

Some times the code will not be able to tell the different between a 0 and a blank. And will try to add the number of days to 12/30/1899.
Is there an expression other than null to prevent this?

 

[pbaldy]

0 isn't Null. How about:

If IsNull(StartDate) Or StartDate = 0 Then Exit Sub

 

[BigHappyDaddy]

pbaldy seems to be pointing you in the right direction, but it is for the precise problem you are having that I prefer using the NZ function and using an IF statement to test for 0 (zero).

[FONT=Arial]Dim StartDate As Date

StartDate = Nz(Me.[TextBox Startdate], 0)

If StartDate = 0 then Exit Sub [/FONT]

Now if StartDate is a NULL or Zero, the IF statement will force an exit.

 

[Jun91]

Again thanks for your support. I have one final question !

Is there a way the code will determine the difference between a 0 and a null? sometimes the code will not exit and will add the number of days to 0.
Resulting in days like Dec.30-1899

If not, its Ok.
Thanks.

 

[BigHappyDaddy]

So that is exactly what this code does....

StartDate = Nz(Me.[Textbox StartDate],0)

The Nz function looks at the value of Me.[Textbox StartDate]. If the value is NULL, then replace that NULL with the value 0 (zero). If the textbox has any non-NULL value then nothing is changed and the value in the textbox is assigned to StartDate.

If StartDate = 0 then Exit Sub

Now StartDate will NOT have a NULL value. So it should be a valid date or 0. If 0, then Exit Sub.

You might want to research more on the Nz function. Very useful in these types of situations.