Disappearing results in query...

[Guest]

Hello,
I'm having some results that I can see in the table disappear in the query
I'm running.
The SQL for the query is as follows: (I don't really know about much of this
stuff...)

SELECT tblstudents.Last, tblstudents.First, tbltraining.[Class Name],
tbltraining.[Class Date], tbltraining.Duration, IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date])) AS ExpireDate
FROM tblstudents INNER JOIN tbltraining ON tblstudents.[Employee Number] =
tbltraining.[Employee Number]
WHERE (((IIf([Duration] Is Null,Null,DateAdd("yyyy",[Duration],[Class
Date])))<[Enter Date:]))
ORDER BY tbltraining.[Class Name];

Uh, yeah.

I have classes that start with the same name: eg Medication I, Medication
II, and Medication III. It's picking up Meds I and III, it can't find them
all when they are taken on different days. If they are taken on the same day,
it shows up fine.

A little confused,

John.

 

[John Vinson]

WHERE (((IIf([Duration] Is Null,Null,DateAdd("yyyy",[Duration],[Class
Date])))<[Enter Date:]))

A criterion of NULL is meaningless and is surely going to give you
problems. I suspect that your missing class has nothing in its
Duration field - what dates would match that class if you don't know
how long the class lasts??


John W. Vinson[MVP]

 

[Guest]

Hey John,
Thanks for your reply.
The duration field simply indicates a year amount. That formula indicates
when a class expires by adding it to the class date. It was a work-around
becuase some classes have no expiry dates, thus the null field in the
duration field.
What could be causing the dates not to show up in the queries?

Thanks,

John.

WHERE (((IIf([Duration] Is Null,Null,DateAdd("yyyy",[Duration],[Class
Date])))<[Enter Date:]))

A criterion of NULL is meaningless and is surely going to give you
problems. I suspect that your missing class has nothing in its
Duration field - what dates would match that class if you don't know
how long the class lasts??


John W. Vinson[MVP]

 

[John Vinson]

Hey John,
Thanks for your reply.
The duration field simply indicates a year amount. That formula indicates
when a class expires by adding it to the class date. It was a work-around
becuase some classes have no expiry dates, thus the null field in the
duration field.
What could be causing the dates not to show up in the queries?

A criterion of

NULL

will match any record where the date field contains the text string
"NULL". Of course there won't be any such.

I'd suggest a criterion on the date of

WHERE DateAdd("YYYY", NZ([Duration], -100), [Class Date]) < [Enter
date:]

This will ensure that a durationless class will always be retrieved
regardless of its class date. Use 0 instead of -100 if you want to
retrieve classes where the class date itself is prior to the entered
date.

John W. Vinson[MVP]

 

[Guest]

Hello John,
Thanks for your feedback.
Actually, using the IsNull does work for the purpose I need it to. If I use
the Nz, it returns a 0 to my formula, which adds 0 to the class date and then
returns the same class date. In other words, the class expires the same day
it was taken. This date shows up in my form.
The goal was to only allow the expiry date to show for classes that have a
number in the duration field. In that way, only classes that actually expire
will have expiration dates.
How is this affecting my query results? Is using the Null causing me to lose
data, even the data that shows up successfully in the form (with the IsNull
formula used?)
The formula in the expiry field is =IIf([Duration] Is
Null,Null,DateAdd("yyyy",[Duration],[Class Date]))

Thanks,
John.

Hey John,
Thanks for your reply.
The duration field simply indicates a year amount. That formula indicates
when a class expires by adding it to the class date. It was a work-around
becuase some classes have no expiry dates, thus the null field in the
duration field.
What could be causing the dates not to show up in the queries?

A criterion of

NULL

will match any record where the date field contains the text string
"NULL". Of course there won't be any such.

I'd suggest a criterion on the date of

WHERE DateAdd("YYYY", NZ([Duration], -100), [Class Date]) < [Enter
date:]

This will ensure that a durationless class will always be retrieved
regardless of its class date. Use 0 instead of -100 if you want to
retrieve classes where the class date itself is prior to the entered
date.

John W. Vinson[MVP]