Annotating and expired date

[Kimberly3626]

Hi all. I have a question regarding if/then statements, expiration dates,
and conditionals...
Here's the situation: I built a database to track briefings and security
clearances. It's doing great, however we need one more function to it, and I
don't know how to do it!
1. There are three types of recognized clearances in the DB in one table.
They are: Secret, Top Secret, and No Valid Clearance. There are three
expiration dates, which are currently not in any table anywhere in the
database: 5 years, 10 years, and n/a (for those without a clearance)
2. I currently have a printed report with the person's information and their
clearance (all pulled from one single table called Soldier Data). What I do
not have is whether or not the clearance is expired. The conflict arises in
that we cannot, at a glance, tell who is expired. The date of initial
clearance is listed, and some are, in fact, expired. However, with the 5 and
10 year expirations, it's hard to notice if a 5-year is expired.

How to I go about tying it all together? In my brainstorming, I have mulled
over:
1. Create another table with the clearance type and the matching expiration
in months. Then...I have no idea how to tie it in to the other table and
make the report tell me if it's expired or not.
2. Create a yes/no field or a simple blank text box that I could fill in
with something as simple as an asterisk to indicate expiration. However, I
need it to be filled in automatically based on the date. Since we did our
initial inputs, three people have expired...I need to know that when I open
the clearance status roster....

Is this even possible? Someone please help!
I'm not necessarily a noob, but at the same time, I'm drawing a blank on
this one.

Thanks in advance.

 

[Mr. B]

Kimberly3626,

From you posting you indicate that the date of the initial clearance is
listed.

It was not clear just how the type of clearance is being identified. For my
solution I am assuming that a combo box provides the three choices (Secret,
Top Secret, No Valid Clearance). Where you have an initial date, you can
create a label control next to the initial date field. Add "Expired" as its
caption, set its visible property to no and place code like the following in
the OnCurrent event of your form and it will display the Expired label (you
could make the control bold and red) when the initial date plus the
appropriate number of years is less than today's date.

Dim dtInitialDate As Date
dtInitialDate = Me.txtInitialDate
Select Case Me.cboClearanceType
Case "Secret"
If DateAdd("yyyy", 5, dtInitialDate) < Date Then
Me.lblExpired.Visible = True
Else
Me.lblExpired.Visible = False
End If

Case "Top Secret"
If DateAdd("yyyy", 5, dtInitialDate) < Date Then
Me.lblExpired.Visible = True
Else
Me.lblExpired.Visible = False
End If
Case Else
Me.lblExpired.Visible = False
End Select

You will of course need to change the control names to match your controls
but hopefully this will help.

HTH
Mr. B
askdoctoraccess dot com

 

[KARL DEWEY]

Try this --
SELECT [Soldier Data].*, IIF([Clearance] = "Top Secret" AND DateAdd("yyyy",
5,[BriefingDate]) >= Date(), "Expired", IIF([Clearance] = "Secret" AND
DateAdd("yyyy", 10,[BriefingDate]) >= Date(), "Expired", IIF([Clearance] =
"Top Secret" AND DateAdd("m", 6, DateAdd("yyyy", 5,[BriefingDate])) >=
Date(), "Will expire in six months or less", IIF([Clearance] = "Secret" AND
DateAdd("m", 6, DateAdd("yyyy", 10,[BriefingDate]) >= Date(), "Will expire in
six months or less", "N/A")))) AS [Clearance Status]
FROM [Soldier Data];

 

[KARL DEWEY]

Change the 6 in the SQL to -6 in two places.