Conditional formula for a date field

[Jonathan Brown]

I've placed an unbound textbox on a form that I want to display an expected
expiration date for a security clearance based on the type of clearance that
it is and when the original clearance was granted. So basically I want it to
do the following: If it's a secret clearance, then I want it to take the date
the clearance was granted and calculate the expiration date 10 years into the
future. If it's a Top Secret clearance then calculate the expiration date 5
years into the future.

I tried the following code and it didn't seem to work.

if me.clearancetype = 1 then
me.expirationdate = me.granteddate + 3650
elseif me.clearancetype = 2 then
me.expirationdate = me.granteddate + 1825
end if

The text box is just a text box. i don' t know how to get it to display a
date.

 

[Jeff Boyce]

Jonathan

You don't mention what this code IS doing. Have you set a breakpoint and
stepped through the code while it runs?

You don't mention where this code is running.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Douglas J. Steele]

Far safer to use the DateAdd function:

If Me.clearancetype = 1 Then
Me.expirationdate = DateAdd("yyyy", 10, Me.granteddate)
ElseIf Me.clearancetype = 2 Then
Me.expirationdate = DateAdd("yyyy", 5, Me.granteddate)
End If

If that still doesn't work, you may need to use the CDate function to ensure
you're actually dealing with a date value:

If Me.clearancetype = 1 Then
Me.expirationdate = DateAdd("yyyy", 10, CDate(Me.granteddate))
ElseIf me.clearancetype = 2 Then
Me.expirationdate = DateAdd("yyyy", 5, CDate(Me.granteddate))
End If

 

[J_Goddard via AccessMonster.com]

Use the DateAdd function:

if me.clearancetype = 1 then
me.expirationdate = dateadd("yyyy",10,me.granteddate)
elseif me.clearancetype = 2 then
me.expirationdate = dateadd("yyyy",10,me.granteddate)
end if

John

 

[Jonathan Brown]

The controls are on a tab control and so what I had done was add the code to
the tab's onclick event. So when that tab control is selected it's supposed
to calculate that value. However, I can't help thinking that the onclick
event for a tab control is the wrong way to go.

I'm not entirely familiar with setting breakpoints to step through the code.
That would be something nice to learn however.

 

[Jonathan Brown]

I'm not sure which event I should add that code to. I had been putting my
code in the onclick event of the tab control on which my controls are placed.
Is there a better way to do that?

 

[Jonathan Brown]

that's awesome. I put that code in the oncurrent event of my form and it
looks beautiful.