Conditional formula for a date field

  • Thread starter Thread starter Jonathan Brown
  • Start date Start date
J

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.
 
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
 
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
 
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
 
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.
 
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?
 
that's awesome. I put that code in the oncurrent event of my form and it
looks beautiful.
 
Back
Top