Auto Calculating date in relation to value in drop down (1 Viewer)

Jun91 · Aug 28, 2017

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 · Aug 28, 2017

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 · Aug 28, 2017

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 · Aug 29, 2017

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 · Aug 29, 2017

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 · Aug 29, 2017

You didn't try? No, try:

Code:

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 · Sep 12, 2017

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 · Sep 12, 2017

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 · Sep 13, 2017

thank you !! finally got it going !!!

pbaldy · Sep 13, 2017

Happy to help!

Jun91 · Sep 14, 2017

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 · Sep 14, 2017

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 · Sep 14, 2017

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

If IsNull(StartDate) then Exit Sub

Jun91 · Sep 15, 2017

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 · Sep 15, 2017

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 · Sep 21, 2017

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 · Sep 21, 2017

0 isn't Null. How about:

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

BigHappyDaddy · Sep 21, 2017

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).

Code:

[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 · Sep 22, 2017

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 · Sep 22, 2017

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

Code:

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.

Code:

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.

Auto Calculating date in relation to value in drop down (1 Viewer)

New member

Wino Moderator

New member

Wino Moderator

New member

Wino Moderator

New member

Wino Moderator

New member

Wino Moderator

New member

Wino Moderator

Wino Moderator

New member

Wino Moderator

New member

Wino Moderator

Coding Monkey Wanna-Be

New member

Coding Monkey Wanna-Be

Similar threads

Users who are viewing this thread