Calculation using current date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

nicole62282 - 28 May 2008 02:51 GMT

I have a db that tracks applicant information for job hiring needs.
Applicants are required to take pre-employment tests and those tests are good
for up to a year. I have 4 tests that each have there own date taken field.
How can I calculate from the current date if it has been a year since they
have taken the tests? I have tried putting in the criteria field of each
date taken source >=[date_taken]-365 but this only returns some of the
fields, but not all. Any help? I can give more info if needed.

Many thank in advance,
Nicole

Reply to this Message

fredg - 28 May 2008 03:33 GMT

> I have a db that tracks applicant information for job hiring needs.
> Applicants are required to take pre-employment tests and those tests are good
[quoted text clipped - 6 lines]
> Many thank in advance,
> Nicole

Where do you wish to do this, in a query?

Elapsed:IIf(DateAdd("yyyy",1,[DateTaken])<Date(),"Not a year
yet","Over a year")

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

John W. Vinson - 28 May 2008 04:39 GMT

>I have a db that tracks applicant information for job hiring needs.
>Applicants are required to take pre-employment tests and those tests are good
[quoted text clipped - 6 lines]
>Many thank in advance,
>Nicole

If you have four testdates in four fields... your table structure could be
better. What if you need a FIFTH test? You'll need to restructure your table,
all your forms, all your queries, all your reports... ouch! A better design
would be three tables: your applicant table; a Tests table (with four rows,
currently); and a TestTaken table with the ApplicantID, TestID, DateTaken, and
test result.

With your current design you need a criterion of

>= DateAdd("yyyy", -1, Date())

on each of the four datefields - but on SEPARATE ROWS of the query grid to use
OR logic.

Signature

John W. Vinson [MVP]

Reply to this Message

nicole62282 - 28 May 2008 14:16 GMT

> >I have a db that tracks applicant information for job hiring needs.
> >Applicants are required to take pre-employment tests and those tests are good
[quoted text clipped - 20 lines]
> on each of the four datefields - but on SEPARATE ROWS of the query grid to use
> OR logic.

I would create the separate tables, but unfortunately there is just too much
information to do this. THis was a db that I inherited at work and there are
7000 applicant records. I see what you are saying "what if a 5th test is
added",...maybe in the future when I get more access savvy I will remember
what you said and build my own db. Thanks so much for your help.

Reply to this Message

Douglas J. Steele - 28 May 2008 15:00 GMT

>> >I have a db that tracks applicant information for job hiring needs.
>> >Applicants are required to take pre-employment tests and those tests are
[quoted text clipped - 39 lines]
> added",...maybe in the future when I get more access savvy I will remember
> what you said and build my own db. Thanks so much for your help.

Are you thinking that 28,000 is too many rows for the second table? That's
small by Access standards.

You can populate that second table from the existing table by creating a
Union query that rearranges the data in the manner you need. If your
existing table is

ExistingTable:
Id
ApplicantName
Test1Date
Test2Date
Test3Date
Test4Date

and your new table will be

NewTable:
Id
TestNumber
TestDate

your Union query would be

SELECT Id, 1 AS TestNumber, Test1Date As TestDate
FROM ExistingTable
UNION
SELECT Id, 2 AS TestNumber, Test2Date As TestDate
FROM ExistingTable
UNION
SELECT Id, 3 AS TestNumber, Test3Date As TestDate
FROM ExistingTable
UNION
SELECT Id, 4 AS TestNumber, Test4Date As TestDate
FROM ExistingTable

Heck, even if you don't correct your design, you can still use that Union
query to simplify looking for out-of-date tests!

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message