How to place a criteria on a calculated field?

[Guest]

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!

 

[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?

 

[Guest]

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.

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!

 

[Guest]

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.

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!

 

[tina]

you're welcome.

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.

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!