First day of the year

  • Thread starter Thread starter Tara
  • Start date Start date
T

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
 
It worked perfectly! Thanks!

Dale Fye said:
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.



Tara said:
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
Click to expand...
Click to expand...
 
Tara said:
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?
Click to expand...

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.
 
Thanks for the technical lesson Rick. Always eager to learn.

Rick Brandt said:
Tara said:
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?
Click to expand...

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.
Click to expand...
 
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


Rick Brandt said:
Tara said:
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?
Click to expand...

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.
Click to expand...
 
Glint said:
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?
Click to expand...

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.
 
Back
Top