formula for expired tests

[James]

I have a form in an access database. This form tells us when our employees have taken their year tests and when the next one is due. This testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly. We have employees that have taken their test this year and colum 3 does state the correct next test for next year yet, they are showing as "expired" as if they had missed this years test. Here is the formula for the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....


Thanks,

James B.

 

[Douglas J. Steele]

Is [next text] a date field, or is it a text field that happens to contain a
date?

If it's text, try:

=IIf(CDate([next test])<Now(),"EXPIRED!",Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a form in an access database. This form tells us when our employees
have taken their year tests and when the next one is due. This testing is
yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly. We have
employees that have taken their test this year and colum 3 does state the
correct next test for next year yet, they are showing as "expired" as if
they had missed this years test. Here is the formula for the "status"
colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....


Thanks,

James B.

 

[James]

it's a date field...

Thanks,
James B.

 

[James]

didn't work.

it's a date field...

Thanks,
James B.

Is [next text] a date field, or is it a text field that happens to
contain a date?

If it's text, try:

=IIf(CDate([next test])<Now(),"EXPIRED!",Null)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have a form in an access database. This form tells us when our
employees have taken their year tests and when the next one is due. This
testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly. We
have employees that have taken their test this year and colum 3 does
state the correct next test for next year yet, they are showing as
"expired" as if they had missed this years test. Here is the formula for
the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....


Thanks,

James B.

 

[John W. Vinson]

I have a form in an access database. This form tells us when our employees have taken their year tests and when the next one is due. This testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly. We have employees that have taken their test this year and colum 3 does state the correct next test for next year yet, they are showing as "expired" as if they had missed this years test. Here is the formula for the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....

What's the context? Is this the control source of a textbox on the form?
Single form or continuous? Is [next text] actually in the form's recordsource?
Is it a table field or a calculated value? What values does it contain for the
erroneous records?

 

[Mike Painter]

I have a form in an access database. This form tells us when our
employees have taken their year tests and when the next one is due.
This testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly.
We have employees that have taken their test this year and colum 3
does state the correct next test for next year yet, they are showing
as "expired" as if they had missed this years test. Here is the
formula for the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....

Try Date() instead of now().
I've seen similar problems with older versions of Access.

 

[James]

Did not work.

James B.

I have a form in an access database. This form tells us when our
employees have taken their year tests and when the next one is due.
This testing is yearly. I have a form that will show:
1) the name of the employee
2) when the test was last taken
3) when the next test should be taken
4) if their testing status is "expired" due to missing a test date

the problem is that 4th colum is not caculating exipired correctly.
We have employees that have taken their test this year and colum 3
does state the correct next test for next year yet, they are showing
as "expired" as if they had missed this years test. Here is the
formula for the "status" colum:

=IIf([next test]<Now(),"EXPIRED!",Null)


Any advice or help would be appriciated....

Try Date() instead of now().
I've seen similar problems with older versions of Access.

 

[John W. Vinson]

Did not work.

James B.

"Doctor, I don't feel good. What should I take?"

For a specific answer, please post a specific question. "Did not work" doesn't
give anyone enough information to help.

 

[James]

"Try Date() instead of now().I've seen similar problems with older versions of Access." Changing to "date" did not work same results as "now" End result: I am getting false expired records.

The date is caculated based on the date the test was taken, manually put into a form, with a formula of "date"yyyy,1,next test......
That date is what is generated on the report and "verified" to be valid or invalid. The form is calculating the date correctly and it is being displayed correctly. when looking at the report, the column that displays the "expired" is =IIf([next test]<Now(),"EXPIRED!",Null). I have tried a compact and repair on the database, I have tried changing now to day.


BTW, I feel fine.

 

[John W. Vinson]

"Try Date() instead of now().I've seen similar problems with older versions of Access." Changing to "date" did not work same results as "now" End result: I am getting false expired records.

Examples please, with the actual data, formula, and results.

The date is caculated based on the date the test was taken, manually put into a form, with a formula of "date"yyyy,1,next test......

That is not a formula that I recognize. What is the actual expression?

That date is what is generated on the report and "verified" to be valid or invalid.

The form is calculating the date correctly and it is being displayed
correctly. when looking at the report,
the column that displays the "expired" is =IIf([next
test]<Now(),"EXPIRED!",Null). I have tried a compact and repair on the
database, I have tried changing now to day.


What is the datatype of [Next test], and what values in that field ar giving
incorrect results? If [Next test] is a Form control, try

=IIF(CDate([Next test]) < Now(), "EXPIRED!", Null)

If it's a Date/Time table field, I don't know what's happening.

 

[James]

Examples please, with the actual data, formula, and results.:

There are 4 columns: member, last test, next test, expired
1) last test - control source last test
2) next test , control source next test
3) expired - control source =IIF([next test])<Date(),"Expired",Null) Again I have tried Now() instead of Date()

User last test next test expired
Test 8/1/2007 8/08 expired

That is not a formula that I recognize. What is the actual expression?

After putting in the date for the test just taken, access runs the following to figure out when the next test is:
=dateadd("yyyy",1,[next test])

That date is what is generated on the report and "verified" to be valid or invalid.

The form is calculating the date correctly and it is being displayed
correctly. when looking at the report,
the column that displays the "expired" is =IIf([next
test]<Now(),"EXPIRED!",Null). I have tried a compact and repair on the
database, I have tried changing now to day.


What is the datatype of [Next test], and what values in that field ar giving
incorrect results? If [Next test] is a Form control, try

=IIF(CDate([Next test]) < Now(), "EXPIRED!", Null)

I tried the above line ^ but, get the same results: please humor me, I can't find the "datatype" of [next test] to tell if it's a date entry. where are you looking for that at?

 

[John W. Vinson]

Examples please, with the actual data, formula, and results.:

There are 4 columns: member, last test, next test, expired
1) last test - control source last test
2) next test , control source next test
3) expired - control source =IIF([next test])<Date(),"Expired",Null) Again I have tried Now() instead of Date()

User last test next test expired
Test 8/1/2007 8/08 expired

That is not a formula that I recognize. What is the actual expression?

After putting in the date for the test just taken, access runs the following to figure out when the next test is:
=dateadd("yyyy",1,[next test])

ok... this makes no sense unless you meant [last test] in the line above; you
can't calculate the next test by adding a year to the next test!!

Try calculating expired based on the actual stored value rather than on
another calculated value:

=IIF(DateAdd("yyyy", 1, [last test])<Date(),"Expired",Null)