Calculation using current date

[nicole62282]

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

 

[fredg]

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

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

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

 

[John W. Vinson]

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

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.

 

[nicole62282]

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

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.

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.

 

[Douglas J. Steele]

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

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.

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.

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!