First day of the year

[Tara]

I have a query that shows data for all current employees, but filters out
those who are no longer employed here by looking to see if the Termination
date field is null or not. What I actually need it to do is show all current
employees plus those who terminated any time during the current year. Any
ideas?

Thanks

 

[Dale Fye]

WHERE ISNULL(TerminationDate) OR Year([TerminationDate]) = Year(Date())

HTH
Dale

 

[Tara]

It worked perfectly! Thanks!

WHERE ISNULL(TerminationDate) OR Year([TerminationDate]) = Year(Date())

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

I have a query that shows data for all current employees, but filters out
those who are no longer employed here by looking to see if the Termination
date field is null or not. What I actually need it to do is show all current
employees plus those who terminated any time during the current year. Any
ideas?

Thanks

 

[Rick Brandt]

I have a query that shows data for all current employees, but filters out
those who are no longer employed here by looking to see if the Termination
date field is null or not. What I actually need it to do is show all current
employees plus those who terminated any time during the current year. Any
ideas?

WHERE ISNULL(TerminationDate) OR Year([TerminationDate]) = Year(Date())

More efficient would be...

WHERE TerminationDate Is Null
OR TerminationDate >= DateSerial(Year(Date()), 1, 1)

Reasons:
Don't use a VBA function (IsNull) in a query when standard SQL will do the job.

Don't apply criteria to expressions containing fields if there is a way to apply
the criteria on a field directly. The former eliminates the ability to use an
index on the field and forces a full table scan.

 

[Dale Fye]

Thanks for the technical lesson Rick. Always eager to learn.

I have a query that shows data for all current employees, but filters out
those who are no longer employed here by looking to see if the
Termination
date field is null or not. What I actually need it to do is show all
current
employees plus those who terminated any time during the current year.
Any
ideas?

WHERE ISNULL(TerminationDate) OR Year([TerminationDate]) = Year(Date())

More efficient would be...

WHERE TerminationDate Is Null
OR TerminationDate >= DateSerial(Year(Date()), 1, 1)

Reasons:
Don't use a VBA function (IsNull) in a query when standard SQL will do the
job.

Don't apply criteria to expressions containing fields if there is a way to
apply the criteria on a field directly. The former eliminates the ability
to use an index on the field and forces a full table scan.

 

[Glint]

Thanks Rick,
Your advice is very incissive. I notice too that when I use IsNull in a
query builder criteria cell, it immediately changes it to IS NULL. What
should I do when the code is in the module in order to take advantage of your
advice?
--
Glint

I have a query that shows data for all current employees, but filters out
those who are no longer employed here by looking to see if the Termination
date field is null or not. What I actually need it to do is show all current
employees plus those who terminated any time during the current year. Any
ideas?

WHERE ISNULL(TerminationDate) OR Year([TerminationDate]) = Year(Date())

More efficient would be...

WHERE TerminationDate Is Null
OR TerminationDate >= DateSerial(Year(Date()), 1, 1)

Reasons:
Don't use a VBA function (IsNull) in a query when standard SQL will do the job.

Don't apply criteria to expressions containing fields if there is a way to apply
the criteria on a field directly. The former eliminates the ability to use an
index on the field and forces a full table scan.

 

[Rick Brandt]

Thanks Rick,
Your advice is very incissive. I notice too that when I use IsNull in
a query builder criteria cell, it immediately changes it to IS NULL.
What should I do when the code is in the module in order to take
advantage of your advice?

If you mean when you are build SQL Statement strings in code the same thing
applies. If you mean something else you will have to clarify. The sql
clause "Is Null" does not work in VBA, but if you are creating SQL strings
then those are evaluated by the Jet query engine, not VBA.