Help building a Date Calculation 2

[vp7799]

This is my 3rd post of the day, hopefully last. I was wondering if it is possible to build an calculation for a date. Here is the problem I work in security investigations. a type A clearance is good for 10 from the investigation date. a Type B clearance is good for 5 years from the investigation date then the clearance becomes a Type A clearance for 10 from the investigation date. If I can have this show in a Table great, if not i need to be able to do some time calculations from it like is the clearance going to expire in 180 days, I already have done the is clearance going to expire in 180 days. One more thing is there a decent book out there that teaches some of this stuff that does not cost a lot of money. Thanks

 

[LittleSmudge]

Let me see if I understand you.

You have a person under investigation.

Record the investigation date in a field - ( call it DateChecked )
and the Investigation type/level in another field - ( call it CheckType )

Then
If CheckType = A then clearance = ( DateChecked + 10 Years )
If CheckType = B then clearance = ( DateChecked + 5 Years )

And you want to somehow flag up when this person is within 180 days of the end of their clearance period ?

Use the SQL string:-
"SELECT * " _
& "FROM tblTableName " _
& &quot;WHERE ( DateChecked < #&quot; & DateAdd('yyyy', 10, Date() + 180 ) & &quot;#&quot; _
& &quot;AND CheckType = 'A' ) &quot; _
& &quot;OR ( DateChecked < #&quot; & DateAdd('yyyy', 5, Date() + 180 ) & &quot;#&quot; _
& &quot;AND CheckType = 'B' ) ;&quot;


You could then put that SQL sting into the RowSource of a comboBox to list the applicable individuals
Or paste it directly into the RecordSource of a Form.

'ope-that-elps.

G LS

 

[vp7799]

Thanks,
Just one more small thing,

When Clearance B expires in 5 years it becomes a Clearance A which will expire 10 years from the orginal investigation date. Did that make sense, and thanks You guys are great

 

[LittleSmudge]

So a Clearance B ???Automatically??? becomes a clearance A ?

Don't you have to verify it ?
Don't you have to check, intervene, inspect - something ?



G LS

 

[vp7799]

Hey,
Kinda, A Clearance B is the best clearance you can get, super secret Squirrel clearance. If at the end of 5 years you do not submit for a reinvestiagtion you automaticly downgrade to a Clearance A which is just Ok nothing speical and is good for 10 years from the orginal investigation date.
Hope that helps with the confusion. BTW do you know of any solid Database books, to help with the higher end stuff, nothing real intense more like immeradiate speed, perhaps with some expresions, or atleast how to build complicated ones. Thanks

 

[SgtJarrow]

I would recommend the Access {insert version here} Developer's Handbook, published by Sybex as one of the best. Sybex also published a Visual Basic Language manual and VBA Handbook as well that quite useful.

I have the Access 2000 edition, by Getz, Litwin, and Gilbert, and it has become my sort of bible and all inclusive resource. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[LittleSmudge]

Oh - right - I hadn't realised that there was two LEVELS as well as two durations.

With you now.

In that case you will have to run a search on a regular basis to find people who's A level has expired and then convert it to a B and reset the dateField


Let me know if you want some more detail.

G LS

 

[vp7799]

Ok I tried the above string and to put it simply I am lost. I want the query If someone has a &quot;SECRET&quot; clearance to add 10 years to the Date of Investigation. If the Investigation is &quot;TOP SECRET&quot; add 5 years to the Date of Investigation to make a Expires date. The SECRET and TOP SECRET are listed in the Clearance column. And I know I messed up already with the spaces in the name. But thanks
Pasted below is the query structure so far.
SELECT [Personal Info].SSN, [Personal Info].[Last Name], [Security Info].Clearance, [Security Info].[Date Of Investigation]
FROM [Personal Info] LEFT JOIN [Security Info] ON [Personal Info].SSN = [Security Info].SSN;

Thanks for any help.