How to place a criteria on a calculated field?

A

Access rookie

Hello,
I have a calculated field called Expiry in my query. It's supposed to show
the expiry date of a class taken. The duration field shows how many years the
class is valid for, so if the class has no duration (Null), then no expiry
date should show. The formula is: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date]))

The problem is, when I put in the criteria e.g <[Enter Date:] , I don't get
all the correct records. The query works fine without the criteria.

Am I doing something wrong somewhere?

Thanks!
 
T

tina

i tested the expression with a parameter, and found that it returned records
with a date in the calculated field (but no "null date" records), after i
set the query parameter to Date/Time. is your query returning no records, or
just some records? if just some, what records are missing?
 
A

Access rookie

Hey Tina,
Thanks for your reply.
I just noticed it is not returning duplicate classes. eg if Employee 7 takes
first aid on 1/1/2000 (expires 1/1/2001) and takes first aid again on
2/1/2001 (expires 2/1/2002) it only returns one of them. Is there a condition
somewhere that prevents duplicates in my subform?

Puzzled,

John.

tina said:
i tested the expression with a parameter, and found that it returned records
with a date in the calculated field (but no "null date" records), after i
set the query parameter to Date/Time. is your query returning no records, or
just some records? if just some, what records are missing?


Access rookie said:
Hello,
I have a calculated field called Expiry in my query. It's supposed to show
the expiry date of a class taken. The duration field shows how many years the
class is valid for, so if the class has no duration (Null), then no expiry
date should show. The formula is: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date]))

The problem is, when I put in the criteria e.g <[Enter Date:] , I don't get
all the correct records. The query works fine without the criteria.

Am I doing something wrong somewhere?

Thanks!
 
A

Access rookie

Hey Tina,

Thanks for your help; I was using a function I didn't quite understand.
This was my statement:

SELECT tblstudents.Last, tblstudents.First, tbltraining.[Class Name],
Max(DateAdd("yyyy",NZ([Duration],20),[Class Date])) AS TrainingExpires
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
GROUP BY tblstudents.Last, tblstudents.First, tbltraining.[Class Name],
tblstudents.[Employee Number]
HAVING (((Max(DateAdd("yyyy",NZ([Duration],20),[Class Date])))<=[Enter
Desired Expiry Date:]));

It was filtering out all but the max date...the brilliant gentleman on this
forum (Dale Fye) helped me with the SQL and explained what it did, but I was
kind of slow in grasping it! Now I understand, and yes, it works!

Thanks again!

John.

tina said:
i tested the expression with a parameter, and found that it returned records
with a date in the calculated field (but no "null date" records), after i
set the query parameter to Date/Time. is your query returning no records, or
just some records? if just some, what records are missing?


Access rookie said:
Hello,
I have a calculated field called Expiry in my query. It's supposed to show
the expiry date of a class taken. The duration field shows how many years the
class is valid for, so if the class has no duration (Null), then no expiry
date should show. The formula is: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date]))

The problem is, when I put in the criteria e.g <[Enter Date:] , I don't get
all the correct records. The query works fine without the criteria.

Am I doing something wrong somewhere?

Thanks!
 
T

tina

you're welcome. :)


Access rookie said:
Hey Tina,

Thanks for your help; I was using a function I didn't quite understand.
This was my statement:

SELECT tblstudents.Last, tblstudents.First, tbltraining.[Class Name],
Max(DateAdd("yyyy",NZ([Duration],20),[Class Date])) AS TrainingExpires
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
GROUP BY tblstudents.Last, tblstudents.First, tbltraining.[Class Name],
tblstudents.[Employee Number]
HAVING (((Max(DateAdd("yyyy",NZ([Duration],20),[Class Date])))<=[Enter
Desired Expiry Date:]));

It was filtering out all but the max date...the brilliant gentleman on this
forum (Dale Fye) helped me with the SQL and explained what it did, but I was
kind of slow in grasping it! Now I understand, and yes, it works!

Thanks again!

John.

tina said:
i tested the expression with a parameter, and found that it returned records
with a date in the calculated field (but no "null date" records), after i
set the query parameter to Date/Time. is your query returning no records, or
just some records? if just some, what records are missing?


Hello,
I have a calculated field called Expiry in my query. It's supposed to show
the expiry date of a class taken. The duration field shows how many
years
the
class is valid for, so if the class has no duration (Null), then no expiry
date should show. The formula is: IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date]))

The problem is, when I put in the criteria e.g <[Enter Date:] , I
don't
get
all the correct records. The query works fine without the criteria.

Am I doing something wrong somewhere?

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top